March 28, 2014 at 3:40 am
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.
March 28, 2014 at 4:33 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2014 at 4:38 am
i am using sql server 2000.
but why there is time difference?
why peoples are suggestung to use batch delete?
March 28, 2014 at 5:39 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2014 at 6:22 am
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?
March 28, 2014 at 6:31 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2014 at 6:47 am
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.
March 28, 2014 at 6:56 am
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
March 28, 2014 at 7:00 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2014 at 7:20 am
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.
March 28, 2014 at 11:50 am
so what is the better solution to delete records in sql server 2000
March 28, 2014 at 12:12 pm
As I said before, your choices in 2000 are more limited. Filter the data on additional criteria to reduce the batch size so you're deleting in smaller chunks and providing the optimizer the ability to come up with a good plan. Using ROWCOUNT doesn't change what the optimizer chooses and if the filtering criteria is not there, the optimizer will choose to scan the data, which is slow.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2014 at 10:03 am
Grant Fritchey (3/28/2014)
As I said before, your choices in 2000 are more limited. Filter the data on additional criteria to reduce the batch size so you're deleting in smaller chunks and providing the optimizer the ability to come up with a good plan. Using ROWCOUNT doesn't change what the optimizer chooses and if the filtering criteria is not there, the optimizer will choose to scan the data, which is slow.
What benefit i will be getting if i move to sq 2008.
March 29, 2014 at 11:23 am
For this particular issue, possibly no real benefit. The problem we are having is that we can't see what you see which means all we can give you are shots in the dark. You have posted nothing that will help us tell you what may help you improve your process. There is no DDL for the table, any indexes on the table, foreign keys (both to and from the table).
Seeing the actual execution plan would also help, but by the time you had that the DELETE would be done as well, unless this is a recurring process that needs to be improved.
March 30, 2014 at 2:26 pm
The only suggestion I had for the higher versions of SQL Server is that you could use the ROW_NUMBER to break up the deletes into smaller groups. You still have to have mechanisms for filtering and choosing how to break the data into smaller groups. Plus everything Jason said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply