Thanks I think this answers my question well! Brett
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Effect of blobs on performance "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. -- D. Richard Hipp <[EMAIL PROTECTED]> ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
