Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Batch Delete is Slow :angry: Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 3:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Batch Delete is Slow
I am deleting some 200000 records from 5000000 rows.

set rowcount 10000
while
begin
Delete from mytable where mytime<=123456
if @@rowcount = 0
break
end
it takes more than 1 min to delete

where as this is fast
Delete from mytable where mytime<=123456
it took only 5 sec to delete.

which one is best to delete bulk of records?
mytime key is clustered index.

Process followed:
table consitsts of 20 columns with int ,varchar,float, double.
Populated mytable record from system table and had some 1/2 million record and deleting the record.
Post #1555810
Posted Friday, March 28, 2014 4:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
Instead of using SET ROWCOUNT, actually filter the data using some other criteria. SET ROWCOUNT is deprecated anyway and removed from the product in SQL Server 2014.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555820
Posted Friday, March 28, 2014 4:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
i am using sql server 2000.
but why there is time difference?
why peoples are suggestung to use batch delete?
Post #1555822
Posted Friday, March 28, 2014 5:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
You're posting in the 2008 forum.

Not sure what you mean by batch delete. SET ROWCOUNT just limits the rows returned, but the query plan and the access of the data isn't modified by ROWCOUNT. So, if you want to actually limit the number of rows, it's better to use additional filtering to reduce the number of rows being accessed so that the optimizer can actually do something different with the execution plan.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555842
Posted Friday, March 28, 2014 6:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Grant Fritchey (3/28/2014)
You're posting in the 2008 forum.

Not sure what you mean by batch delete. SET ROWCOUNT just limits the rows returned, but the query plan and the access of the data isn't modified by ROWCOUNT. So, if you want to actually limit the number of rows, it's better to use additional filtering to reduce the number of rows being accessed so that the optimizer can actually do something different with the execution plan.


Then what is best way to delete bulk of Records in the table with minimal time and minimal locking.
Atucally i ma having fkey reference in 10 tables , when i try to delete some 200000 records from my main table whcih is having 1/2 million records it is taking more then 30 min to delete. (nornal delete not the batch delete, batch delete takes 40 min to delete)

Thats why i had an example of same to delete a table without any Fkey reference, it took only 1 min to delete.

So my question is Fkey is blocking the delete operation? if so how to overcum this? droping and recreating Fkey is the best way to do in production?
Post #1555860
Posted Friday, March 28, 2014 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
Yeah, it's pretty likely that FKs are checking each constraint. You can look at the execution plan to confirm this, but I'm sure it's accurate. If you are doing very large scale deletes, or inserts, it's fairly common to drop the foreign keys first. You just have to make sure you have good scripts that won't leave orphans and, when you recreate the FKs, you use the WITH CHECK option.

But, again, the way to limit locking and blocking is to reduce the number of rows being deleted at one time. And the way to do that is not to use ROWCOUNT, but to filter the records through the WHERE clause in some other fashion. On 2000, you have a lot fewer options than if you were on 2005 or better.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555861
Posted Friday, March 28, 2014 6:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Grant Fritchey (3/28/2014)
If you are doing very large scale deletes, or inserts, it's fairly common to drop the foreign keys first. You just have to make sure you have good scripts that won't leave orphans and, when you recreate the FKs, you use the WITH CHECK option.

But, again, the way to limit locking and blocking is to reduce the number of rows being deleted at one time. And the way to do that is not to use ROWCOUNT, but to filter the records through the WHERE clause in some other fashion. On 2000, you have a lot fewer options than if you were on 2005 or better.


1) Ok, Say i am droppping foreign keys and start deleting the records,what will happen in the middle of delete , if the server is broken, how to reenable the Foreign keys .
2) While running the Delete statement if i stop the query how to reenable the foreign keys(As you said Orphans) any links for that to restore orphans.

I using SQL Server 2000 only plan to migrate to sql 2005 in middle of next year until that i have to use SQL 2000.So finding best way to deal with delete statements.
Post #1555871
Posted Friday, March 28, 2014 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Here in the KB article SET ROWCOUNT has been used to delete large records.
http://support.microsoft.com/kb/323630
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

Post #1555877
Posted Friday, March 28, 2014 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
yuvipoy (3/28/2014)

1) Ok, Say i am droppping foreign keys and start deleting the records,what will happen in the middle of delete , if the server is broken, how to reenable the Foreign keys .
2) While running the Delete statement if i stop the query how to reenable the foreign keys(As you said Orphans) any links for that to restore orphans.

I using SQL Server 2000 only plan to migrate to sql 2005 in middle of next year until that i have to use SQL 2000.So finding best way to deal with delete statements.


If the scripts stop in the middle, you have problems. There's no getting around that. You'd have to have some secondary step that recreates the FKs, but, if it stopped in a partial state, you could get orphans, yes. There are no simple answers here.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555879
Posted Friday, March 28, 2014 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
The system is Standalone system.There will not be any user to see if there is an problem or not.
It should be auto- clear the issues.
That is main problem i am facing. Thats why i did not go for disabling and deleting and enabling the Fkeys.
There wont be any manual users to track the issue.
Post #1555886
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse