Very slow Delete from table with large BLOB data

Question

Post reply

Very slow Delete from table with large BLOB data

NabeelM

SSC-Addicted

Points: 448
More actions
April 13, 2012 at 8:09 am

#267323

Hi guys,
First time posting to this forum. I am having issues trying to delete from a table with very few rows but each row has large BLOB in it. The table has about 200 rows and I am trying to delete about 50 rows from this table but it seems to take forever and blocks SQL server doing anything else. Deleting even a single row takes very long time and sometimes doesn't return. Also, something that might be relevant is that data is continuously being pumped into this table.
Here is the table:
---------------------------------------------------
CREATE TABLE [dbo].[DATA_ARCHIVE](
[DEPLOYMENT_ID] [varchar](30) NOT NULL,
[NAME] [varchar](870) NOT NULL,
[BLOB] [image] NULL,
[DATETIME] [datetime] NULL,
[DESCRIPTION] [varchar](4000) NULL,
[BLOB_COMPRESS_FLG] [int] NULL,
[CLEANUP_TIME] [datetime] NULL,
[META_DATA] [image] NULL,
CONSTRAINT [PK_ZEN_MARKET_DATA_ARCHIVE] PRIMARY KEY CLUSTERED
(
[DEPLOYMENT_ID] ASC,
[NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_DEFAULT]
) ON [FG_DEFAULT] TEXTIMAGE_ON [FG_DEFAULT]
GO
Here is my delete statement:
------------------------------------
DELETE
FROM dbo.DATA_ARCHIVE
WHERE CLEANUP_TIME<CURRENT_TIMESTAMP
Any help would be appreciated. Thanks.
Nabeel
April 13, 2012 at 8:40 am

This was removed by the editor as SPAM
April 13, 2012 at 8:56 am

This was removed by the editor as SPAM
April 16, 2012 at 12:57 am

This was removed by the editor as SPAM
April 16, 2012 at 6:45 am

This was removed by the editor as SPAM
April 16, 2012 at 7:14 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

NabeelM SSC-Addicted Points: 448 More actions · Answer 1

That's the whole table. There are no other indexes.

NabeelM SSC-Addicted Points: 448 More actions · Answer 2

Thanks for that. I have created another index on CLEANUP_TIME but that didn't help. Actually, I believe this is not a seek issue. The performance is really slow (unuseable) even if I search based on the PK.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

And for what it is worth the image is deprecated. You should instead use varbinary(max).

http://msdn.microsoft.com/en-us/library/ms187993%28v=sql.105%29.aspx

You may find some benefits by removing these huge columns from this table into either their own table or use FILESTREAM.

Also you may find some benefits from using an identity for your PK in this case. You currently have a composite key that is very wide. I am guessing that the Name column varies quite a bit. This can cause some performance issues because the size of the pk varies so much. Index fragmentation and that sort of thing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NabeelM SSC-Addicted Points: 448 More actions · Answer 4

Thanks for your replies. Appreciate it.

I have tried these solutions and although it did improve the performance but it is still way too slow. Since it's an existing database which we are plugging into, we don't have the luxury to change stuff. To narrow down the problem I have created a separate copy of the same table with just 50 rows and a IDENTITY column as surrogate PRIMARY KEY. Now even if I do "DELETE FROM" that table without any condition or based on the new ID it takes forever. I think the problem is deleting rows with huge blobs. TRUNCATE works almost instantly.

I am not sure why DELETE is taking that much time. Does it do something with the blob that will slow it down? Any way to speed this up>

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Try to update the image columns to null. Does it take same as long as delete?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

NabeelM SSC-Addicted Points: 448 More actions · Answer 6

Yep, setting IMAGE to null takes as long as well. 🙁

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

That is showing what really takes the time! It's getting rid of large BLOBs.

I'm afraid, you can not do much without redesign...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

vikingDBA SSCertifiable Points: 5356 More actions · Answer 8

No idea if this will help, but if you are deleting tens of thousands of rows, you don't have a commit, so it might have some trouble with deleting that many blobs without a commit somewhere to break it up into batches.

Ex: Keep deleting batches of 25 thousand rows until all are deleted, with commits at each group.

Could this be an issue?

PiMané SSChampion Points: 13965 More actions · Answer 9

I think that having a VARCHAR(870) field on the index doesn't help much either...

Do you need an index with the NAME field on it?

If you need to work better, try working less...