Re: BLOB question

From strictly a performance standpoint, I'd select option #1. IMHO I'd guess it would have a slight edge.

My application is very similar, except I treat the BLOB as out of band data and access it seperately from all the other fields. So for my application
there would be virtually no difference between the two options.

That being said, what's dominating our design is the backup/restore issues. With so many BLOB being stored and our typically user model is 99% write-once,
read-many. We're planning to put the BLOBs in massively partitioned tables based on time. Once a partition is "filled" we'd set it to read-only and not need to
back it up as often. This means we need to treat all BLOB tables as read-only
and implement a "copy on modification" strategy just for BLOB data.

So in our case, option #2 then becomes the hands down winner.

• Gordon

<marciat_at_my-deja.com> wrote in message news:8s00mj$ppl$1_at_nnrp1.deja.com...
> I have two scenario's and am curious if anyone has strong feelings
> about one approach over the other.
>
> I need to store a blob in a database, and could potentially be adding
> 30,000 or more each day to the table that holds them. I would like to
> keep a history of edits to this blob also so a table of the following
> design is one approach:
>
>
> table Object_Blob
> object_id integer (primary key)(foriegn key)
> user_id integer (primary key)(foriegn key)
> object_date date/time (primary key)
> object blob
>
>
> Another approach follows the theory that any table with a blob should
> only contain the blob and a single field as a key, such as:
>
> table Object_blob
> blob_id integer (primary key)
> object blob
>
> table Object_blob_reference
> object_id integer (primary key)(foriegn key)
> user_id integer (primary key)(foriegn key)
> object_date date/time (primary key)
> blob_id integer
>
> In the first approach, with all the fields with the exception of the
> blob being the primary key, the RDBMS should consider that a single
> field. With the right secondary indexes I should be able to go where I
> want in the table correct? Would there be any extra performance gains
> with respect to locating the blob by following the second approach over
> the first?
>
> Thanks to all in advance
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Oct 11 2000 - 15:12:22 CEST