Hello, sorry for the lengthy email.
I feel like I'm missing something, but I don't know what - is there anyone
who could at least help me with a direction to search in.
I'm using Windows XP Sp2, SQLite3.DLL V3.2.7. One of the tests which follows
was repeated with bleeding edge SQLite3.DLL V3.3.1 - and gave the same
result.
If I do the following query 'SELECT count(*) FROM MyTable' then when I time
the process I get the following results:
(All times in milliseconds) (For ~17000 records)
87 81 86 84 85 231 1132 1142 1138 1137
Pseudo code is:
{
sqlite3_open;
for(i=0;i<10;i++)
doQuery;
sqlite3_close;
}
doQuery code is:
{
sqlite3_prepare;
StartTimer;
while(sqlite3_step <> SQLITE_DONE)
;
RecordTime;
sqlite3_finalize;
}
Note: NO other sqlite3 commands other than indicated are issued
If I call sqlite3_open and sqlite3_close in doQuery I get similar results.
This was done to check the possibility that the database was getting stale
or something.
If I call LoadLibrary and FreeLibrary in doQuery I get similar results. This
was done to check the possibility that the DLL was doing something bad.
As mentioned in my previous email - I can narrow it down even further. The
ONLY sqlite3_step command that has a problem is the final one that returns
SQLITE_DONE.
The only thing that seems to make it quick again is terminating and
restarting the process - i.e. shutting down and restarting my program. It
seems that no matter what I do it's quick for about 5 queries, and then
seems to fall in a heap.
A bigger table of 700,000 records just makes the problem happen sooner - I
only get 1 or 2 quick accesses - quick is a relative phrase - the query time
at first is 6 seconds and then degrades to 12 seconds.
With 2450 records it's 12 queries at 30 milliseconds each then 350
milliseconds per query.
With 1100 records it's 21 queries at 14 milliseconds each then 130
milliseconds per query.
With 680 records it's 27 queries at 9 milliseconds each and then the query
time alternates between ~20ms and ~40ms. Ooooh - this last one is
interesting - it's starting to feel like a memory allocation thing.
Is there a call to sqlite that I should be doing to release memory?
I don't mind doing the work to persue a course of action / testing, but
could somebody please give me an idea as to the best course of action.
Regards,
Carl.
