Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.