Slow queries (measuring SQLite3Step execution time vs. queries in SQLiteSpy, by factor ~25)

Aug 29, 2014 at 10:44 AM
Edited Aug 29, 2014 at 11:18 AM

Edit: Disregard this post. I've optimized the database now, and queries are at least roughly on par with queries in SQLiteSpy. Maybe SQLiteSpy is doing some internal optimizations that I wasn't aware of which used to speed up the queries...

Original post:

Hi,

I've been using this library for a while now.
Recently I've noticed that queries seem to take extremely (well, in comparison) long. I'm querying a database that contains 11 columns and ca 24,000 records, sorting by a certain column and fetching the latest 99 entries of it.

To rule out any other function (swriting the results into a collection/array etc.) distorting the results, I've measured execution time right in the SQLite3Step function with a relatively precise
Private Declare Function GetTickCount Lib "kernel32" () As Long
call.
The SQLite3Step call of my query takes around 1500 milliseconds to execute. Now, I've executed the very same query for the very same database using SQLiteSpy, and it only took 55ms.

I'm out of ideas why though; SQLiteStep3 isn't doing much more than calling the dll directly, I don't see why there is such a massive delay.

For the record - when I drop the WHERE and ORDER BY parts of the query, execution time drops from 1500ms to around 200ms.
None of my tables is in any way optimized (no clever setting of primary keys, indexing etc.). But I don't think this should cause any difference; I don't think SQLiteSpy is doing any further optimizations to the query, particularly not when it's being run for the very first time. So I don't see why this should cause any dramatic difference in execution times.

So I'm a bit out of ideas why there is such a huge latency here. Do you have any idea what might be the issue?
Coordinator
Aug 29, 2014 at 2:21 PM
Hi,

One factor might be the particular build of SQLite3.dll that you use. I just included what I could download from sqlite.org, but maybe SQLiteSpy has a custom build of the library. There's a whole bunch of compile-time flags and of course compiler optimizations.
You might try to make your own build of SQLite3.dll and experiment a bit from there.

But I certainly would not expect the VBA wrapper to be slow or behave funny.

-Govert