On February 21, 2007, [EMAIL PROTECTED] wrote: > "Brett Keating" <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I'm curious about what the effect of having a blob in the database may > > be on performance. I have two design options: 1) put a small image file > > (15-30kbyte) into the database as a blob, and 2) store the image in a > > separate file on disk and hold the filename in the database. My table > > has around 20 rows in it, about half are strings/smaller blobs and half > > are integers. > > > > Option number one, for various reasons, is far more elegant and simple > > in terms of its impact on the rest of the code. However, I am concerned > > that holding such large amounts of data per record might impact > > performance. I could be worried about nothing though, which is why I'm > > writing to this list :). > > When I was designing the SQLite file format, I made the assumption > that BLOBs would be used infrequently and would not be very big. > The file format design is not optimized for storing BLOBs. Indeed, > BLOBs are stored as a singly-linked list of database pages. It > is hard to imagine a more inefficient design. > > Much to my surprise, people begin putting multi-megabyte BLOBs > in SQLite databases and reporting that performance really was not > an issue. I have lately taken up this practice myself and routinely > uses SQLite database with BLOBs that are over 10MiB is size. And > it all seems to work pretty well here on my Linux workstation. I > have no explanation for why it works so well, but it does so I'm not > going to complain. > > If your images are only 30KiB, you should have no problems. > > Here's a hint though - make the BLOB columns the last column in > your tables. Or even store the BLOBs in a separate table which > only has two columns: an integer primary key and the blob itself, > and then access the BLOB content using a join if you need to. > If you put various small integer fields after the BLOB, then > SQLite has to scan through the entire BLOB content (following > the linked list of disk pages) to get to the integer fields at > the end, and that definitely can slow you down.
I still wonder about the utility of storing binary data in the db itself. Maybe it makes it more easy to distribute that way, but how often does one distribute an entire database in a "vendor specific" format? I'm quite interested in hearing people's reasoning for going the blob route, when you have a perfectly good "database" format for "blobs" already (various filesystems). > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > --------------------------------------------------------------------------- >-- To unsubscribe, send email to [EMAIL PROTECTED] > --------------------------------------------------------------------------- >-- -- Thomas Fjellstrom [EMAIL PROTECTED] ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
