delete BLOB objects

When I delete rows from a table that contains an NTEXT column, I get a delet
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql