Delete query taking too long

Question

Delete query taking too long

savibp3

Right there with Babe

Points: 752
More actions
July 14, 2017 at 5:42 am

#402294

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

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

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

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)