Very slow Delete from table with large BLOB data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Very slow Delete from table with large BLOB...

18 posts, Page 1 of 2

1 2 »»

Very slow Delete from table with large BLOB data

Rate Topic

Display Mode

Topic Options

Author

Message

NabeelM

Posted Friday, April 13, 2012 8:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:24 AM
Points: 6, Visits: 35

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

Post #1283168

Stewart "Arturius" Campbell

Posted Friday, April 13, 2012 8:40 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129, Visits: 4,312

What indexes (over and above the primary key) exist on the table?

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1283187

NabeelM

Posted Friday, April 13, 2012 8:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:24 AM
Points: 6, Visits: 35

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

Post #1283194

Stewart "Arturius" Campbell

Posted Friday, April 13, 2012 8:56 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129, Visits: 4,312

Consider adding an index on the CLEANUP_TIME column.
This should speed up the delete action substantially (even more so if it were made a clustered index - however, this would mean the PRIMARY KEY would have to be changed to NONCLUSTERED (DROPped and reCREATEed))

Herewith an example of how this could be accomplished:

ALTER TABLE [dbo].[DATA_ARCHIVE]
DROP CONSTRAINT PK_ZEN_MARKET_DATA_ARCHIVE
GO
ALTER TABLE [dbo].[DATA_ARCHIVE]
ADD CONSTRAINT PK_ZEN_MARKET_DATA_ARCHIVE PRIMARY KEY NONCLUSTERED (DEPLOYMENT_ID] ASC,[NAME] ASC)
GO
CREATE CLUSTERED INDEX IX_DATA_ARCHIVE_CLEANUP_TIME ON [dbo].[DATA_ARCHIVE]([CLEANUP_TIME])
GO

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1283205

NabeelM

Posted Friday, April 13, 2012 9:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:24 AM
Points: 6, Visits: 35

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.

Post #1283222

Sean Lange

Posted Friday, April 13, 2012 9:31 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1283232

Stewart "Arturius" Campbell

Posted Monday, April 16, 2012 12:57 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129, Visits: 4,312

Sean Lange (4/13/2012)

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.

I must agree with Sean in this regard.

Adding an INT IDENTITY() column to the table and making that your NONCLUSTERED PK should drastically improve performance. It is never a good idea to have such a wide PK (900 byte in this case). Then you could consider creating a NONCLUSTERED index on the name and / or ID column.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1283904

NabeelM

Posted Monday, April 16, 2012 5:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:24 AM
Points: 6, Visits: 35

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>

Post #1284021

Eugene Elutin

Posted Monday, April 16, 2012 6:44 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:00 AM
Points: 2,543, Visits: 4,384

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1284063

Stewart "Arturius" Campbell

Posted Monday, April 16, 2012 6:45 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129, Visits: 4,312

NabeelM (4/16/2012)

TRUNCATE works almost instantly.

That is a given, as TRUNCATE deallocates the data page(s) for said table

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1284064

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions