Batch Delete is Slow :angry:

  • yuvipoy

    SSChampion

    Points: 10431

    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.

  • Grant Fritchey

    SSC Guru

    Points: 395226

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • yuvipoy

    SSChampion

    Points: 10431

    i am using sql server 2000.

    but why there is time difference?

    why peoples are suggestung to use batch delete?

  • Grant Fritchey

    SSC Guru

    Points: 395226

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • yuvipoy

    SSChampion

    Points: 10431

    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?

  • Grant Fritchey

    SSC Guru

    Points: 395226

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • yuvipoy

    SSChampion

    Points: 10431

    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.

  • yuvipoy

    SSChampion

    Points: 10431

    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

  • Grant Fritchey

    SSC Guru

    Points: 395226

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • yuvipoy

    SSChampion

    Points: 10431

    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.

  • yuvipoy

    SSChampion

    Points: 10431

    so what is the better solution to delete records in sql server 2000

  • Grant Fritchey

    SSC Guru

    Points: 395226

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • yuvipoy

    SSChampion

    Points: 10431

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442091

    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.

  • Grant Fritchey

    SSC Guru

    Points: 395226

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply