December 21, 2010 at 11:16 am
I have a table where it is faster to delete one row at a time using a cursor than it is to delete all the records using a query.
I would like to change it to a set-based delete but am baffled as to why the cursor version is faster. I can delete 20000 records in 5 seconds using the cursor version whereas I cancelled the set-based version after 8 minutes and not one record had been deleted.
Any ideas as to why the cursor version is faster (or why the set-based one doesn't work)?
December 21, 2010 at 12:26 pm
is the delete statement within the cursor the same as the standard delete statement?
the cursor might be hitting a good index within its statement.
what are your SQL statement?
December 21, 2010 at 12:34 pm
I think seeing the code for both methods would be essential to answering this question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 12:53 pm
--Table definition (with relevant indexes)
CREATE TABLE [dbo].[tblDocument](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[Category] [varchar](50) NULL,
[Number] [varchar](50) NULL,
[State] [varchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [varchar](50) NULL,
[ModifiedDate] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
[DirType] [int] NULL,
[DocumentName] [varchar](200) NULL,
[FileName] [varchar](200) NULL,
[Box] [varchar](50) NULL,
[Screen] [varchar](50) NULL,
[Notes] [varchar](max) NULL,
[PurgedFlag] [tinyint] NULL,
[PurgedDate] [datetime] NULL,
[PurgedBy] [varchar](50) NULL,
[RetainFlag] [tinyint] NULL,
[RetainDate] [datetime] NULL,
[RetainBy] [varchar](50) NULL,
CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
(
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_tblDocument_Category_Number] ON [dbo].[tblDocument]
(
[Category] ASC,
[Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_tblDocument_CreatedDate_DocumentID] ON [dbo].[tblDocument]
(
[CreatedDate] ASC,
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
--Does not work
delete tblDocument
from tblDocument as d
left outer join cdm_data as cdm
on cdm.ApplicationOrg = left(d.Number, 3)
and cdm.ApplicationType = substring(d.Number, 4, 3)
and cdm.ApplicationNumber = right(d.Number, 13)
where d.Category = 'APPLICATION'
and d.CreatedDate < convert(varchar(10), getdate(), 101)
and cdm.ApplicationNumber is NULL
--Works fast (20000 records in 5 seconds)
declare @DocumentID as int
declare curDoc for
select DocumentID
from tblDocument as d
left outer join cdm_data as cdm
on cdm.ApplicationOrg = left(d.Number, 3)
and cdm.ApplicationType = substring(d.Number, 4, 3)
and cdm.ApplicationNumber = right(d.Number, 13)
where d.Category = 'APPLICATION'
and d.CreatedDate < convert(varchar(10), getdate(), 101)
and cdm.ApplicationNumber is NULL
open curDoc
fetch curDoc into @DocumentID
while @@fetch_status = 0
begin
delete from tblDocument where DocumentID = @DocumentID
fetch curDoc into @DocumentID
end
close curDoc
deallocate curDoc
December 21, 2010 at 1:02 pm
--Does not work
delete tblDocument
from tblDocument as d
left outer join cdm_data as cdm
on cdm.ApplicationOrg = left(d.Number, 3)
and cdm.ApplicationType = substring(d.Number, 4, 3)
and cdm.ApplicationNumber = right(d.Number, 13)
where d.Category = 'APPLICATION'
and d.CreatedDate < convert(varchar(10), getdate(), 101)
and cdm.ApplicationNumber is NULL
--Works fast (20000 records in 5 seconds)
declare @DocumentID as int
declare curDoc for
select DocumentID
from tblDocument as d
left outer join cdm_data as cdm
on cdm.ApplicationOrg = left(d.Number, 3)
and cdm.ApplicationType = substring(d.Number, 4, 3)
and cdm.ApplicationNumber = right(d.Number, 13)
where d.Category = 'APPLICATION'
and d.CreatedDate < convert(varchar(10), getdate(), 101)
and cdm.ApplicationNumber is NULL
open curDoc
fetch curDoc into @DocumentID
while @@fetch_status = 0
begin
delete from tblDocument where DocumentID = @DocumentID
fetch curDoc into @DocumentID
end
close curDoc
deallocate curDoc
I think this would be faster
select DocumentID
into #StageDelete
from tblDocument as d
left outer join cdm_data as cdm
on cdm.ApplicationOrg = left(d.Number, 3)
and cdm.ApplicationType = substring(d.Number, 4, 3)
and cdm.ApplicationNumber = right(d.Number, 13)
where d.Category = 'APPLICATION'
and d.CreatedDate < convert(varchar(10), getdate(), 101)
and cdm.ApplicationNumber is NULL
delete td
from tblDocument td
Inner Join #StageDelete SD
On sd.documentid = td.documentid
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 1:09 pm
I tried deleting just 10 records directly as follows:
delete from tblDocument where DocumentID in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
The delete ran for over a minute, then I cancelled it.
But doing the following was fast (mere seconds):
delete from tblDocument where DocumentID = 1
delete from tblDocument where DocumentID = 2
delete from tblDocument where DocumentID = 3
delete from tblDocument where DocumentID = 4
delete from tblDocument where DocumentID = 5
delete from tblDocument where DocumentID = 6
delete from tblDocument where DocumentID = 7
delete from tblDocument where DocumentID = 8
delete from tblDocument where DocumentID = 9
delete from tblDocument where DocumentID = 10
That's why I used a cursor. The single row delete was faster than using a list of ID's for a single delete.
But it doesn't make any sense to me as to why the cursor version would be faster.
December 21, 2010 at 1:32 pm
How is the index fragmentation for that table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 2:12 pm
Is this what you are looking for? If not, how do I get it?
Index Name/avg_fragmentation_in_percent
IX_tblDocument_Box 18.7529747739172
IX_tblDocument_Category_Number 8.07453416149068
IX_tblDocument_Category_State 34.336917562724
IX_tblDocument_CreatedDate_DocumentID 26.4388489208633
IX_tblDocument_DocumentID 7.15277777777778
IX_tblDocument_DocumentName 41.025641025641
IX_tblDocument_PurgedDate 26.8115942028986
IX_tblDocument_RetainFlag 14.6437346437346
PK_tblDocument 60.941960038059
December 21, 2010 at 2:20 pm
Roger Sabin (12/21/2010)
Is this what you are looking for? If not, how do I get it?Index Name/avg_fragmentation_in_percent
IX_tblDocument_Box 18.7529747739172
IX_tblDocument_Category_Number 8.07453416149068
IX_tblDocument_Category_State 34.336917562724
IX_tblDocument_CreatedDate_DocumentID 26.4388489208633
IX_tblDocument_DocumentID 7.15277777777778
IX_tblDocument_DocumentName 41.025641025641
IX_tblDocument_PurgedDate 26.8115942028986
IX_tblDocument_RetainFlag 14.6437346437346
PK_tblDocument 60.941960038059
It looks like you have a duplicate index on DocumentID (PK and the NCI IX_tblDocument_DocumentID). Your clustered index is fragmented to nearly 61%. I would evaluate the need for that second index on documentid and then defrag your indexes. Then check performance of your delete.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 2:35 pm
Sorry, should have left IX_tblDocument_DocumentID off the list. I created it after I tried the 2 different kinds of deletes. I was thinking that maybe the clustered index was the problem and was going to replace it with the non-clustered index. Never finished.
December 21, 2010 at 3:06 pm
Rebuild your clustered index and retry the deletes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2010 at 8:26 pm
Roger Sabin (12/21/2010)
I tried deleting just 10 records directly as follows:delete from tblDocument where DocumentID in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
The delete ran for over a minute, then I cancelled it.
Any chance there is a trigger on the table you are trying to delete from that is causing the problem? (I'm thinking that it can handle a single row delete, but can't handle a set based delete.)
December 23, 2010 at 12:58 am
Roger Sabin (12/21/2010)
I tried deleting just 10 records directly as follows:delete from tblDocument where DocumentID in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
The delete ran for over a minute, then I cancelled it.
But doing the following was fast (mere seconds):
delete from tblDocument where DocumentID = 1
delete from tblDocument where DocumentID = 2
delete from tblDocument where DocumentID = 3
delete from tblDocument where DocumentID = 4
delete from tblDocument where DocumentID = 5
delete from tblDocument where DocumentID = 6
delete from tblDocument where DocumentID = 7
delete from tblDocument where DocumentID = 8
delete from tblDocument where DocumentID = 9
delete from tblDocument where DocumentID = 10
That's why I used a cursor. The single row delete was faster than using a list of ID's for a single delete.
But it doesn't make any sense to me as to why the cursor version would be faster.
"Mere" seconds to delete only 10 rows? Something is definitely not right with that machine or the indexes. 10 rows should take only milliseconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2010 at 9:18 am
Do we have a status update on this?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2010 at 10:46 am
There are no triggers on the table.
I did not time the actual deletes but when 10 single deletes finish quickly and a single delete for 10 rows does not, I think it was obvious which one was faster.
I have not had time to rebuild the clustered index and try deleting again. I went with the cursor deletes because I needed to get something done now and it was accomplishing the deletes in a reasonable amount of time.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply