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. 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(*) FRM 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_DNE)
;
RecordTime;
sqlite3_finalize;
}
Note: N 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
NLY sqlite3_step command that has a problem is the final one that returns
SQLITE_DNE.
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. - 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.

1/16/06, Carl Jacobs <Carl (AT) jacobsdesign (DOT) com.auwrote:

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.

This sounds like memory exhaustion or a memory leak.

Carl Jacobs wrote:

>I'm using Windows XP Sp2, SQLite3.DLL V3.2.7. 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(*) FRM 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
>
>

>
>As mentioned in my previous email - I can narrow it down even further. The
>NLY sqlite3_step command that has a problem is the final one that returns
>SQLITE_DNE.
>
>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.
>
>
>

Carl,

What language are you using to drive SQLite?

Carl Jacobs wrote:

>I'm using Windows XP Sp2, SQLite3.DLL V3.2.7. 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(*) FRM 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
>
>As mentioned in my previous email - I can narrow it down even further. The
>NLY sqlite3_step command that has a problem is the final one that returns
>SQLITE_DNE.
>
>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.
>

Carl,

Sorry for the repost, I hit send too soon last time.

What language and/or wrapper library are you using to drive SQLite?

Also, how large are the records in your database? You said earlier that
they have 45 fields, but not what type of data was stored in any of
them. Since you are doing string searches, I assume that at least one
contains a string. Is it a large string?

Actually it would be best if you could provide the actual schema of the
table, including any indexes and triggers that are defined as well.

Dennis Cote

What language and/or wrapper library are you using to drive SQLite?

Also, how large are the records in your database? You said earlier that
they have 45 fields, but not what type of data was stored in any of
them. Since you are doing string searches, I assume that at least one
contains a string. Is it a large string?

Actually it would be best if you could provide the actual schema of the
table, including any indexes and triggers that are defined as well.

Dennis,
Thanks for your response.

Since my last email I've done some more testing and have narrowed the
problem down considerably. Your "What language" question is quite perceptive
and seems to be at the core of the problem. I'm using Delphi, but basically
talking direct to SQLite3.DLL. After further testing yesterday I accidently
discovered that the problem was NLY happening when I ran my program from
the Delphi IDE. I've never noticed any slowdown like this before, but don't
call very many external DLL's from my programs. The code running in the IDE
(with slowdown) didn't have any breakpoints set, and the code that I run
outside of the IDE (without problem) is the same compile as the code that I
ran inside the IDE.

I discovered the nature of the problem accidently when I recompiled the
source for SQLite V3.3.1 under Visual Studio to try and see if I could find
the slowdown point. I'm *very* new to Visual Studio but managed to get the
debugger to break inside the DLL after having it being called from my App -
as long as my App was not being executed in the Delphi IDE - and in the
process discovered that the problem was no longer present!

Ah well, I guess I'll just have to check the SQLite performance of my App
outside the Delphi IDE.

Regards,
Carl.

Hello Carl Jacobs,

You are using Delphi? Have you checked out my DISQLite3 Delphi port which does not require the sqlite3.dll but compiles directly into any executable:

I have not seen any of the IDE problems you described when running / debugging applications with my no-dll Delphi port of SQLite. However, I am curious if it shows the same behaviour with your code, so it would be great if you could report your findings or make available the Delphi code so I can test this myself?

Thanks and regards,

Ralf

>I'm using Delphi, but basically talking direct to SQLite3.DLL. After further testing yesterday I accidently discovered that the problem was NLY happening when I ran my program from the Delphi IDE.