Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performance Issue with Database Connection
I have an app that runs several queries depending on user input. The data
source is an Access database that's right at the 2GB limit. I stored numerous queries in the database to cover all the possible user selections from the Excel app. I'd read that stored queries are a bit faster than just sending the SQL script. Database resides on a LAN Average time on these varies from 2 1/2 mins to 5 mins. From listening to users (especially my boss) we need to improve this time. I've tried every way I could think of to run this and nothing shortens the time. 99.5% of the wait is while opening the recordsets. My question: Due to the size of this db (and its only one table but with 1.5M records) would moving this to a SQL table and querying off of that speed this up? I do some other stuff against our main SQL tables and they are painfully slow, but that's with lots of joins and far more records. This would just be the one table. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performance Issue with Database Connection
Hi
You should take this to a database newsgroup for more qualified solutions (with all respect, spreadsheet collegues :-) Have you experimented with indexes for performance gain ? If not then do so (on a copy): http://www.microsoft-accesssolutions...erformance.htm Best wishes Harald "Ctal" skrev i melding .com... I have an app that runs several queries depending on user input. The data source is an Access database that's right at the 2GB limit. I stored numerous queries in the database to cover all the possible user selections from the Excel app. I'd read that stored queries are a bit faster than just sending the SQL script. Database resides on a LAN Average time on these varies from 2 1/2 mins to 5 mins. From listening to users (especially my boss) we need to improve this time. I've tried every way I could think of to run this and nothing shortens the time. 99.5% of the wait is while opening the recordsets. My question: Due to the size of this db (and its only one table but with 1.5M records) would moving this to a SQL table and querying off of that speed this up? I do some other stuff against our main SQL tables and they are painfully slow, but that's with lots of joins and far more records. This would just be the one table. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performance Issue with Database Connection
Hi,
I think you mix up with stored procedures. They are a lot faster but can only be handeled by MSSQL. You can upsize MSAccess to MSDE which is a "light" MSSQl with the upsize wizzard in MSAccess. Than people can connect to the server that runs (the there stored procedures) to get the data they want to their own PC's.The size limit for MSAccess and MSDE is 2GB. But that is for each table.So you can have more tables as long as each is less than 2GB. If you use linked tables than there is no size limit , because Access just stores the pointers. If you have more than a few users than going from Access (JET) to MSSQL will again speedup things considerably. If you want an experts view than post at : microsoft.public.vb.database.ado hth Gys "Ctal" wrote in message .com... I have an app that runs several queries depending on user input. The data source is an Access database that's right at the 2GB limit. I stored numerous queries in the database to cover all the possible user selections from the Excel app. I'd read that stored queries are a bit faster than just sending the SQL script. Database resides on a LAN Average time on these varies from 2 1/2 mins to 5 mins. From listening to users (especially my boss) we need to improve this time. I've tried every way I could think of to run this and nothing shortens the time. 99.5% of the wait is while opening the recordsets. My question: Due to the size of this db (and its only one table but with 1.5M records) would moving this to a SQL table and querying off of that speed this up? I do some other stuff against our main SQL tables and they are painfully slow, but that's with lots of joins and far more records. This would just be the one table. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performance Issue with Database Connection
Your queries maybe inefficient or there
maybe something wrong with the VBA code. If you post the code and queries you might get a suggestion how to improve the speed. RBS "Ctal" wrote in message .com... I have an app that runs several queries depending on user input. The data source is an Access database that's right at the 2GB limit. I stored numerous queries in the database to cover all the possible user selections from the Excel app. I'd read that stored queries are a bit faster than just sending the SQL script. Database resides on a LAN Average time on these varies from 2 1/2 mins to 5 mins. From listening to users (especially my boss) we need to improve this time. I've tried every way I could think of to run this and nothing shortens the time. 99.5% of the wait is while opening the recordsets. My question: Due to the size of this db (and its only one table but with 1.5M records) would moving this to a SQL table and querying off of that speed this up? I do some other stuff against our main SQL tables and they are painfully slow, but that's with lots of joins and far more records. This would just be the one table. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Performance Issue with Database Connection
"RB Smissaert" wrote ...
Your queries maybe inefficient or there maybe something wrong with the VBA code. If you post the code and queries you might get a suggestion how to improve the speed. And post the table schema i.e. it may be flaw in the design. With a Jet db on a network, you want to reduce the number of data pages that get sent, so you will have to pay some regard to physical storage. If you have an integer primary key (e.g. an IDENTITY autnumber), read on... The clustered index determines the physical order of the table i.e. how it is stored on disk (the rows' physical order is the same as their logical order). Therefore, a clustered index is particularly efficient on columns that are often searched for ranges of values e.g. an application that frequently executes a query to retrieve records between a range of dates. You only get one clustered index per table so choosing it wisely is one of the biggest performance boosts you can give to your application. The obvious analogy is a telephone directory, which is physically ordered by surname and first names respectively. This is its clustered index, it couldn't possibly have another clustered index (physical order). With Jet, the primary key is always the clustered index, you have no choice in the matter. One of the strongest arguments against an IDENTITY/autonumber PK (or a manually-increasing integer, for that matter) in Jet is that a unique monotonic integer makes a poor choice for a clustered index e.g. how many times are you going to want to search a range of telephone numbers? You can get round Jet's limitation by making the PK a composite of a more meaningful column and the integer column *in that order*. Note that with Jet the a table is only physically reordered on the clustered index when the database file is compacted; while a compact is pending, any new rows will physically appended to the table i.e. be in logical but not physical order. So to rectify, DROP the existing PK, define a new composite primary key with the first column being your most often used index column and, if you truly really need it for uniqueness, your integer column last. Then ensure the clustered index is being regularly rebuilt. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Performance Issue | Excel Discussion (Misc queries) | |||
Excel Performance issue | Excel Discussion (Misc queries) | |||
Excel Viewer performance issue | Excel Discussion (Misc queries) | |||
how do i set up employee performance database in excel | Excel Worksheet Functions | |||
Iteration performance issue | Excel Programming |