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 10000), 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
