Michael,
The guy who produced Sqlitespy is a member of this forum so he can
confirm or debunk my theory as to why you are getting a big difference
in execution time. I suspect that Sqlitespy might be storing the SQL in
its compiled (from sqlite3_prepare) form and when you run it you skip
the compile phase. It is hard to imagine that compile optimization
makes a fourfold difference in executions speed, particularly to a well
written program like Sqlite.
We do a similar thing in our Sqlite applications, compile the SQL when
the program loads then use the compiled VDBE code as input to
sqlite3_bind and sqlite3_step at execution time. It kicks up
performance no end and also traps any schema mismatches before any
processing starts.
Note that if you use the sqlite3_exec function you cannot have
pre-compiled SQL because sqlite3_exec is a wrapper around
sqlite3_prepare/step/reset/finalize. It is a big waste of processing
time to be compiling the same statment thousands of times unnecessarily.
If you use sqlite3_exec I suggest that you replace it. It is only there
for compatibility with legacy programs.
If you use gcc you can use option -finline-functions to get some extra
speed at the cost of a larger executable, although Sqlite is not written
with thousands of one line functions so the improvement will not be great.
michael cuthbertson wrote:
Thanks to Christian and John for the pointers regarding compilers.
I have not compiled the sqlite sources myself but have used the supplied
binary.
Could either one you give me some tips for compiling the sqlite sources for
either vs 6 or 8?
John, I will follow your advice on inline functions.
The absolute last worry I have is the size of my exe.
My concerns are speed (1) and runtime memory (2), i.e., in-mem db size.
BTW, here are some times to demonstrate what I am facing:
A table with 14 columns, 8 indexes, only 4k rows.
This is a secondary(subset) table - not a view - that I created since
running this
query against my complete table of 440k rows was impossibly slow.
The following query takes 75ms in my code - just the SQL_exec and
callbacks -
while SQLiteSpy takes 20ms, including display.
select * from (select f1, f2, f3, f4, f5
from table where f6 = 2563351070 and f2 <='2006-01-01' and f2 >=
'2004-01-01')
order by f1 limit 32 offset 855;
This qry is used to refresh a scrolling display where any of the constants
are actually variables.
The subquery returns about 1000 rows.
Note that a LIMIT = 1 is only marginally faster than 32.
Also, as the offset increases, the exec. time increases about 50% as fast,
which I do not understand,
since this would merely seem to be an index into the result set that should
be low cost.
