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. Oracle has this, and maintains in-row only a "lob index", which basically points to the "pages" of the lob (applies to character lobs too, which SQLite would store as text). Assuming a 4-byte page index, and an 8K page, that's 2,000x less data to rewrite on row updates. You still have to rewrite the row I think, since a single byte update to a blob implies an update to the index to maintain transaction integrity (and Oracle's MVCC), but you're rewriting a lot less data. Until SQLite introduces such "blob" specific pages and a similar level of indirection (for both blob and text), I'm afraid SQLite cannot reasonably to used for some use cases, which happen to matter to what I'm doing. A new pragma to define a threshold after which large blob and text columns switch from in-row to out-of-row storage would just make my day :). I'm neither an SQLite expert, nor an Oracle expert, so please feel free to correct the above if it's not true or incorrect in any way. Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
