On 15.06.2011 04:51, David Michael wrote:
One requirement I have is to be able to handle large blobs, 10-15MB.
BLOB performance is frequently debated. See here for pros and cons and some
authoritative answers:
http://www.mail-archive.com/sqlite-users@xxxxxxxxxx/msg22240.html
Increasing the pagesize of the database actually doesn't increase the DB
file size as much I first thought. The DB gets bigger, but not at the
rate I was worried about.
For larger page sizes, a slight increase in database size is expected because
smaller pages can be filled more evenly.
And the bigger pagesize does have a nice impact on large blob saving
(and on loading).
Writing out a database page to disk is a single operation and pretty much
optimized by the operating system. Larger pages therefore tend to be write
faster in relation to the amount of data written. You might want to test if
setting the page size equal to the hard drive's sector size has some impact as
well.
I'm not familiar enough with SQLite, though, to know if ratcheting the
pagesize up like this is a *good* idea.
SQLite was not primarily designed to handle extremely large BLOBs. It does
handle them, though, but testing is necessary to figure out the best settings
for a particular system. If a larger pagesize works well for you, I guess it is
a good idea to use it.
Again, any information/suggestions appreciated.
It is recommended that large BLOBs be stored in a separate table with only an
INTEGER PRIMARY KEY.
This is because SQLite updates an entire row at a time. So when you change the
integer, it has to rewrite both the integer and the BLOB.
SQLite has to do this because the fields of a row are of varying length.
Integers, for example, are stored in between 1 and 9 bytes depending on their
magnitude. If you change the value of an integer it might change the amount of
storage it requires, which then requires rewriting everything that comes
afterward, including megabytes of BLOB data.
If performance is paramount, also look at PRAGMA synchronous = OFF;. It
generally speeds up insertions at the expense of saftey. Increasing PRAGMA
cache_size might also help somewhat. And last but not least, always remember to
store your data inside a transaction.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
