Thanks for the quick reply. I was lysdexic, I meant to say 20 columns. Probably would never exceed 20,000 rows, most likely would hover around 2-4K rows in a typical situation.
If it has no effect on performance, I'd rather hold it in the database because I do like the idea of having a "neat package" so to speak. For instance when a record is deleted, I'd rather not worry about having to check to see if it holds an image file and then go delete that file... And if I delete many items at once with one statement, I'd have to break it up into individual deletes and check each one in order to delete my corresponding image file on disk... Otherwise I'll get these orphaned image files all over the place on my disk that correspond to deleted records. I'm fairly sure disk space requirements will be nearly identical in each case... Just worried about query/update performance. Thanks, Brett -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Wednesday, February 21, 2007 1:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Effect of blobs on performance On 2/21/07, 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 :). > unless you haven't told us the complete story, you are worried about nothing probably. You have only 20 rows, fer crying out loud, you could probably build an application with chalk and slate and it would be fast enough ;-) that said, there is much recurring discussion on this on another list that I am on (folks wanting to store remote sensing imagery into PostGres). Most concur that it is a ridiculous idea... just store the metadata including the image path in the db, and then let the filesystem do the heavy lifting. Usually one can't do much db level analysis on blobs anyway, so storing them in the db becomes pointless other than the fact that it is a nice neat single bundle. Well, wonder if you could utilize the concept of packages the way they are on OS X. >From the outside, a package looks like a file... you can grab it, copy it, move it. Actually it is a folder in which you can have your core db as well as all the images. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
