Delete query taking too long

  • I have a history table which takes 15 mins to complete.
    Delete from table_name where dt_bus =@dt_start.
    Please advice can i add index on dt_bus column to fix

  • savibp3 - Friday, July 14, 2017 5:42 AM

    I have a history table which takes 15 mins to complete.
    Delete from table_name where dt_bus =@dt_start.
    Please advice can i add index on dt_bus column to fix

    Probably. Try doing that on a QA version of your database to test it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • savibp3 - Friday, July 14, 2017 5:42 AM

    I have a history table which takes 15 mins to complete.
    Delete from table_name where dt_bus =@dt_start.
    Please advice can i add index on dt_bus column to fix

    Take a look at the execution plan before you add the index. Save it. Add the index. Look at the execution plan again. Compare the two. Do this as well as measure the performance. It's a fundamental part of understanding if adding indexes helps.

    "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

  • savibp3 - Friday, July 14, 2017 5:42 AM

    I have a history table which takes 15 mins to complete.
    Delete from table_name where dt_bus =@dt_start.
    Please advice can i add index on dt_bus column to fix

    Does your history table have FKs that are pointing at other tables or point to it?  Does it have any triggers on it?  Also, can you post the DDL for the history table, any keys (including FKs) that it has on it, and any indexes that it may have on it?  We also need to know the range of dates that dt_bus currently has in it, how many rows the table currently contains, and we need to see the query you're using to do the DELETEs.

    Otherwise, the answer is "You can, but it may be a total waste of time, effort, disk space, backup space, and index/stats maintenance time/resource usage waste until you do some other things first".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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