April 13, 2012 at 8:09 am
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:44 am
That's the whole table. There are no other indexes.
April 13, 2012 at 9:22 am
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.
April 13, 2012 at 9:31 am
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/
April 16, 2012 at 5:26 am
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>
April 16, 2012 at 6:44 am
Try to update the image columns to null. Does it take same as long as delete?
April 16, 2012 at 6:49 am
Yep, setting IMAGE to null takes as long as well. 🙁
April 16, 2012 at 7:05 am
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...
April 17, 2012 at 8:18 am
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?
April 17, 2012 at 8:26 am
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?
April 17, 2012 at 11:05 am
With that small number of rows and rows-to-delete, I am guessing you were simply blocked. Run sp_who2 active while you are trying to do the delete to see who is blocking your delete action. If it isn't blocking then there is something REALLY wrong with your server. I could store/delete that much data on my cell phone in near real-time. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2012 at 3:24 pm
How is data continually pumped in to this table if it only has 200 rows?
April 18, 2012 at 4:35 am
Those 200 rows are just for sample data I am testing with. So I run a process that adds data and then clean up previously added data. We are expecting thousands of rows per day.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply