On Thu, Mar 7, 2013 at 3:50 AM, Dominique Devienne <ddevie...@gmail.com>wrote:
> On Tue, Mar 5, 2013 at 4:05 PM, Richard Hipp <d...@sqlite.org> wrote:
> > Both implementations allow for reading just the prefix of the content
> blob
> > in order to access earlier fields of a table, leaving the tail of the
> blob
> > unread on disk.
>
> This information sparked a bit of a debate among my colleagues, because
> since SQLite's back-end is paged, some of them assumed that pages
> corresponding (entirely) to un-selected (blob) columns would be
> skipped/seeked over, thus avoiding to be read from disk, even if followed
> by other columns that are selected.
>
> Am I reading you correctly that this assumption is incorrect?
>
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. (When I was designing
the SQLite3 file format, 10 years ago, I analyzed every SQLite2 database
file I could lay my hands on and found that it was very unusual to have a
TEXT or BLOB column that exceeded a few hundred bytes in length, so this
was a reasonable thing to do back then. Since that time, usage patterns
have changed, perhaps because SQLite3 is very efficient at handling large
BLOBs and TEXT fields even with its linked-list overflow chain
implementation.)
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?
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
