Subject Re: Store Blobs in Seperate Tables?
Author Adam
--- In, "Shaq" <shaq@c...> wrote:
> Is it best to store blobs in a seperate table? Does it increase
> performance any or is it negligible? I am storing photos from a web
> camera which are about 340k uncompressed in size.
> -Shaq


I think you mean should you store BLOBs in a separate database table or
in the same database table. That apparently does not matter for
performance, with one exception.

Updating or deleting a record puts a lock on that record that prevents
any other transaction modifying it until you commit or rollback
(obviously it doesn't affect selects, because writers dont block
readers). I learnt last week that modifying a record does not duplicate
the BLOB, but rather copies the blobs reference in the new record, so
that is not an issue, but if you have operations that modify the data
in the same table as the image, and you have operations that modify the
image itself, and you want different transactions to be able to
simultaneously modify them (unusual, but I suppose it is possible),
then putting it in a different table will allow for this.

Then as Aage points out, there is the whole argument about whether to
store the images in the database, or just reference a file path to the
image, both options should be considered. Having a larger database can
be a disadvantage with respect to backup time (especially if the images
themselves never change once they are inserted).