Optimization

[MikeDutcher]

My Access database links to ~43,000 record table on SQL SERVER.

Extensive use of VBA, small tables read into arrays, complex array calculations (actuarial), public variables (retain info as query moves in and out of VBA), scores of functions.

I'm optimizing the VBA (replacing IIFs with IF THEN ELSE where options involve calls to complex functions).

Query was running in 3 minutes, 10 seconds.

After some revisions, 2 minutes & 10 seconds (1 minute is big as a million record input file looms)

Hooray!! Save this version. And so I do.

Now if I so much as add a '*' to a VBA comment, I'm back to ~ 3 minutes run time.

I copy back my fast version. Still fast. But any VBA revision 'ruins' the speed.

Any ideas as to why the 'stars are aligned' on my fast version and why they become 'unaligned' with the least provocation?

 

[JeremyNYC]

Mike,

What if you muck with things and then compile all your code? Does your speed return?

Also, I do a lot of work with sql statement in my code but I'm slowly moving that stuff to stored queries, as they're faster. I don't know how much that will help in your case, but it might be something to think about.

Also, if you're using SQL Server you probably know about indexing for speed, but if not, check into it.

Hmm. What else???

Someone posted a great link here to a document that had gazillions of tips for speeding things up, but I didn't bookmark it. I know I responded in the thread, and that it was within the last month. I can't remember more than that about it, though.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.