Improve inserting larger BLOBs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin Koppmann - 27 Aug 2004 15:02 GMT

Hi!

We are saving 100 byte to 6 mb BLOBs in a SQL Server database.

If the size reaches about 2mb I am apruptly experiencing very long delays
when calling the .Update procedure of the recordset. It does not make any
difference If we use AppendChunk or not. I also tried ADODB.Stream.

The delay seems to occur if the appended blob size reaches an internal
limit. Is there any hidden setting?

If this behaviour is by design, would it be possible to append chunks with
several updates?
Unfortunatels AppendChunk is resetted after one .Update.

Thank you,
Martin

P.S.: I already considered pros and cons of saving blobs in DB, but the data
has to be saved in the DB.

Reply to this Message

Ralph - 28 Aug 2004 20:18 GMT

> Hi!
>
[quoted text clipped - 16 lines]
> P.S.: I already considered pros and cons of saving blobs in DB, but the data
> has to be saved in the DB.

What version of SQL Server are you using? (I only mention this because there
are some subtle differences between 2k and 7.0 when it comes to storage and
options.)

I doubt very much that a particular ADO method is the solution and the
problem is more related to how sqlserver stores the data. Externally the
data appears as just a long string of bytes - exactly how you put it there
in the first place. However, internally row associated data is stored in 8k
pages, anything more gets stored in 8k pages in a btree with a 16-bit index,
up to a certain size where then intermediate btrees for indexes are created
which then point to another ... blah, blah, blah.
At some point it has to become an unwieldly way to store a 'stream' of
something.

You should check with one of the sqlserver newsgroups, but it appears you
are not completely done examing 'all' the pros and cons of saving blobs in
the db. <g>

You did mention that some are less than 8k. SQL Server 2000 allows you to
save these items directly in the row, which would definately speed up I/O.
You might consider a slight redesign where some middle tier gauges size and
chooses a different storage and retrival policy. The new Instead_Of trigger
might play a role.

Hopefully for your sake someone will reply and tell me I am completely
wrong. but I believe you are either going to have to live with the slow
response, or redesign your storage.

hth
-ralph

Reply to this Message

Martin Koppmann - 30 Aug 2004 16:31 GMT

Thank you for your considerations, Ralph!
I might have to consider a different apporach.

We have SQL7, SQL 2K and Jet 4.0 in use and the symptoms are the same.
Therefore I thought it is a general ADO problem and I might have to use
another method to put a byte junk into the database.

Martin

> What version of SQL Server are you using? (I only mention this because
> there
[quoted text clipped - 33 lines]
> hth
> -ralph

Reply to this Message

VB Forum / Database Access / August 2004

Improve inserting larger BLOBs