Need Access performance suggestions

[BrooksR]

Does anyone has any suggestions on how to improve performance on a processing intensive Access application. There is a small frontend that is about 9 MB. There are 2 backend databases. One with static data that is almost 1 GB. The main table is 3 million rows of stock option quotes. The other backend database is 31 MB and contains tables that grow by a couple thousand records during the run. The main processing is doing queries against the 3 million row table. The run currently takes about 5 hours. All the tables have primary keys and indexes matching the where conditions in queries. Any thoughts or suggestions are appreciated. I'm using Access 2007 and all databases are ACCDBs.

 

[Andrzejek]

I’m sure there are tools to do that in Access, but I would use the simple (read: lazy, easy) approach first. 5 hours of processing, that’s a long time. I’m sure (I hope) you have your code split into some procedures / tasks. I would want to know where the ‘bottle-necks’ are, which pieces of my code take the most of the processing time. If, let’s say, you have 20 procedures that run, and most of them take a few second, and two or three of them take 2 hours each, I would concentrate first on those ‘problem’ pieces and try to fix (evaluate) them first.
You can track them down as simple as writing some info into a text file ([tt]Procedure name, Now()[/tt] ), or Insert this data into a simple table in your DB. And after the run, analyze the data created.


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Have you tried running the built-in performance analyser?

 

[BrooksR]

Thanks for the post, Andy. I'm a professional programmer, so I write modular code. I've been doing tracing and haven't found any real bottlenecks yet. I'm doing thousands of select queries against a 3 million row table with a primary key consisting of 4 fields.

strongm - yes, I did run the analyzer but it didn't really help as much of SQL code is in VBA modules.

Brooks

 

[Andrzejek]

thousands of select queries against a 3 million row table " that's a kind of a red flag to me.
I used to use this approach, before I've discovered [tt]RecordSet.Filter[/tt], which speeds up my processing big time.

Just a suggestion...


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

> much of SQL code is in VBA modules

Is it safe to assume that there is no actual VBA in the queries? That can seriously slow things down ...