On 3 Dec 2009, at 6:26pm, mr_orange wrote:
> I am trying to optimize the speed of my SQLite transactions. The goal is to
> beat query and insertion times that we have with MS SQL. I guess there are 2
> main issues:
>
> 1) The query times are faster when I do simple select statements on a large
> amount of data. I start to run into trouble when I get into more complex
> join statement that involve date conversion functions. I've read that
> replacing joins with "WHERE" statements that involve consideration of the
> ordering of tables in FROM statement can help with speed. However, I am a
> little skeptical since I think that this consideration was already made in
> the current implementation.
Regard the matching parameters of 'JOIN' as the same as 'WHERE'. In other
words, to make them happen fastest, JOIN on indexed columns. If you are going
to join on the results of a calculation (e.g. date conversion function) store
the result of the calculation in its own column and include that column in an
appropriate index.
Remember: the majority of effort SQL puts into a SELECT instruction is all to
do with the WHERE clause (and in your case, the JOIN, which is another WHERE
clause). Understand how those work and you'll understand what's taking all the
time.
> 2) I've tried ramping up the "cache_size" (from 2000 to 1), but I don't
> see any effect, even with simple select statements. Does this seem strange?
Forget that sort of tuning. You're going to get your improvements by
understanding how indexes are used.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
