On Thu, Mar 7, 2013 at 11:50 AM, Richard Hipp <d...@sqlite.org> wrote: > When a row is larger than a single page, the content spills into multiple > pages where the pages are strung together as a linked list. You have to > read each page in order to follow the linked list.
That makes sense. Thanks for the precision. > [...] That said, if you enable incremental vacuum mode > > PRAGMA auto_vacuum=INCREMENTAL; > VACUUM; > > Then SQLite3 will store some additional information related to each page > (specifically pointer map pages described at > http://www.sqlite.org/fileformat2.html#ovflpgs) which will often enable it > to follow the linked list of overflow pages without actually having to read > each page in the chain. Can you rerun your performance measurements after > switching to incremental vacuum mode and see if that makes a difference? Makes a big difference! The vacuum itself was not free (7+ sec) , but sped up the couple queries accessing the tail columns from 1,500+ ms to ~50 ms. Not everyday you get a 30x improvement :) Thanks a bunch for this insight. --DD PS: FWIW, I'm running this on a Win64 Core i7 laptop, on the SSD drive. C:\Users\DDevienne>sqlite3 toseekornottoseek.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA auto_vacuum=INCREMENTAL; sqlite> create table t (head number, big blob, tail text); sqlite> insert into t values (1, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (2, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (3, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (4, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (5, randomblob(100*1024*1024), "100 MB"); sqlite> .timer ON sqlite> VACUUM; CPU Time: user 1.996813 sys 7.550448 sqlite> .q C:\Users\DDevienne>sqlite3 toseekornottoseek.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .timer ON sqlite> select head, tail from t; CPU Time: user 0.046800 sys 0.046800 sqlite> select tail from t; CPU Time: user 0.046800 sys 0.046800 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
