A
Andrew Smith
I spent today trying to speed up my database (generally successfully I'm
pleased to say!)
I came across one particular query that took rather a long time (between 400
and 2500milliseconds) and this had a significant impact on overall
performance as it is executed quite frequently. This query was actually a
SQL string generated from the VBA code and is used to open a recordset.
To improve performance I tried creating a saved parameter query. After
saving the query I ran it manually to ensure it was compiled. The first time
I ran it it was slow. After that it was practically instant, so this
appeared to be working.
I then modified the code so that the recordset was opened from a query def
object based on this query (with the parameter values passed to the query
def by the code). Then I tried reruning the timing tests, and the time fell
to about 80 milliseconds - a fantastic improvement. Unfortunately this
improvement only happened the first time. After that it went back to the
original two and a half seconds. If I ran the query manually then I could
reproduce the behaviour - it would execute in about 80 ms once, and then go
back to 2 seconds or more.
Finally I did a compact and repair on the database, and since then I've been
getting a consistent 400ms - better that I was getting to start with, but
clearly not as good as the best.
There are probably other things I can do to speed this up, but I can't
really spend any more time on it (especially as it's now running much faster
due to some other things I've done). I assume that the behaviour is due to
the query becoming decompiled after the first time it's run by the code. If
anyone knows how to stop this then I'd be grateful.
pleased to say!)
I came across one particular query that took rather a long time (between 400
and 2500milliseconds) and this had a significant impact on overall
performance as it is executed quite frequently. This query was actually a
SQL string generated from the VBA code and is used to open a recordset.
To improve performance I tried creating a saved parameter query. After
saving the query I ran it manually to ensure it was compiled. The first time
I ran it it was slow. After that it was practically instant, so this
appeared to be working.
I then modified the code so that the recordset was opened from a query def
object based on this query (with the parameter values passed to the query
def by the code). Then I tried reruning the timing tests, and the time fell
to about 80 milliseconds - a fantastic improvement. Unfortunately this
improvement only happened the first time. After that it went back to the
original two and a half seconds. If I ran the query manually then I could
reproduce the behaviour - it would execute in about 80 ms once, and then go
back to 2 seconds or more.
Finally I did a compact and repair on the database, and since then I've been
getting a consistent 400ms - better that I was getting to start with, but
clearly not as good as the best.
There are probably other things I can do to speed this up, but I can't
really spend any more time on it (especially as it's now running much faster
due to some other things I've done). I assume that the behaviour is due to
the query becoming decompiled after the first time it's run by the code. If
anyone knows how to stop this then I'd be grateful.