Also, I have question about the cache_size pragma. If I run the query (this is regarding the count query), without first running ANALYZE on the database, I can see that the times are affected by the cache_size pragma as follows:
cache_size = 0 timing = 3.7 s cache_size = -2000 (2000 kb) timing = 2.0 s cache_size = -1000000 (1000000 kb) timing = 2.0 s The size of the database is about 100 MB containing about 10000 rows. The calls are all done from a freshly started database connection (but the timings don't change if I repeat the query). If however I run analyze, the general timings go down (to a timing in the order of tens of milliseconds) and the cache_size doesn't have an effect (or at least a substantial one). Are there any tips to make sure that the cache is used properly? I also tried to put the database on a directory mounted in RAM and didn't get an improvement in speed. (However, I wasn't able to load my dataset into a memory db because, by using .dump the fts5 table doesn't get backed up correctly). Thanks, Gabriele On Fri, Apr 21, 2017 at 10:34 AM, Gabriele Lanaro <gabriele.lan...@gmail.com > wrote: > Thanks for your answers! > > First of all, my apologies with ANALYZE I meant that it increased > performance (decreased query time) by orders of magnitude. > > Before ANALYZE the query was in the order of seconds (about 2 seconds). > After ANALYZE the query was in the order of milliseconds. > > I tried VACUUM too, but didn't improve query time. > > I'm now trying saving the result to a temporary table (I set > temp_store=memory to avoid writing on disk), it looks like, by saving only > the rank, the query takes about 50 ms, which is a very good time.. > > And then extracting the first 15 records ordered by rank becomes very fast > thanks to an index. This is already very useful because it may happen that > we query the same term over and over to scroll down the result by rank. > > Any other tips are always appreciated. > > Thanks again! > > Gabriele > > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
