On 09/15/2014 06:19 PM, Dominique Devienne wrote:
On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
On 09/15/2014 03:18 PM, Dominique Devienne wrote:
On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith <eas....@gmail.com>
wrote:
Looking at the sqlite web site and mailing lists shows that the SQLite
team
has taken a stab at answering the question, "is it faster to read a blob
out of sqlite or out of a file?". See the links below.
Does the team have analogous guidance regarding write speeds?
Depends if you care only about the initial write, or also about
incremental
writes.
SQLite is transactional, but does not have a level of indirection between
the row data, and the blob data. i.e. the blob is "in row". So whenever
you
want to update a single byte of a blob, you must rewrite the whole row,
i.e. the whole blob(s) in that row (and I think this applies to any column
of the row, not only blob columns). This is just prohibitive, and
unmanageable in practice.
I think the only exception to this is if you use the incremental-blob API:
http://www.sqlite.org/c3ref/blob_open.html
What do you mean Dan? Are you saying that opening an existing blob, writing
1 byte somewhere, and closing it, does not yield the whole row to be
updated? Which therefore implies copying the whole old row value somewhere
for undo? (or writing the new row value to the WAL, old value + bytes
changed). Is this somehow not transactional? or SQLite supports sub-row or
sub-page deltas to implement its transactions? I'm confused by your remark.
SQLite always writes entire pages to disk. But if your blob is large
enough it will be spread over multiple overflow pages. In this case if
you update a single byte of the blob using the incremental-blob API,
only the single modified page is updated on disk - not all the other
pages that store data from the same row.
Dan.
I'm not saying SQLite lacks an API to do incremental updates to blobs, but
that its implementation and current file format lacks a way to implement
them efficiently as of now, like a file system can. Basically it want to
use SQLite as a mini transactional filesystem (w/o any need for directory
operations, just file ones, like SQLite own VFL more or less), in libraries
that like SQLite support a VFL abstraction. I'd implement those libraries
VFLs on top of SQLite blob incremental APIs. It's possible, but nowhere
near efficient enough ATM, for the partial update case which is required by
those libraries. Just imagine how slow SQLite itself would be, if you based
its own VFL on the SQLite's blob APIs. Nest an "inner" SQLite DB inside a
blob inside the "outer" SQLite, and you'll get what I mean I think, which
is that doing any SQL update on the inner DB implies doing copies of the
whole inner DB. --DD
_______________________________________________
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
