An 8X speedup is a pretty good achievement...congrats...
#1 I take it your query is CPU bound the first time? #2 Can you show us the query planner please? #3 Can you show us the query planner minus the "INDEXED BY"? #4 Can you show us sqlite_stat1? #5 Can you show us your tables now? #6 What happens if you do "pragma cache_size=15000"? I'd make cache_size = (readbytes/8192)*1.1 at least. #7 Care to post your database again? It still seems to me this should run faster the first time unless you have a really slow disk system or sqlite is doing something silly (which I doubt). Perhaps the cache being to small is hurting things. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Григорий Григоренко [grigore...@mail.ru] Sent: Monday, July 25, 2011 3:30 AM To: sqlite-users Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow Okay, here are some steps I've done. 1) normalize db; I've created single table (item). "computer","process" and "who" fields in log table became rowid integers pointing to this table. "kind" is still a short string. 2) give up covering indexes; For those not aware (<thismessage:/>http://www.sqlite.org/queryplanner.html , 1.7 Covering Indices) it's an index that has additional columns at it's end ( being selected by query). Covering index eliminate the need to read data from db records. But it increases size of index and size of database. 3) use INDEXED BY to suggest index to query planner. Since indexes are created exactly for specific query. Software was working during weekend. Yesterday's database has 6 mln records and it's only 1.1 Gb in size. Comparing to 4 mln records and 3.5 Gb size before. Now, the long running query took 27 seconds and it has read 50 Mb from database (compare to 2 minutes and 307 Mb before). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
