Is that prohibitively expensive to delete few records from large table?

Question

Is that prohibitively expensive to delete few records from large table?

Eliyahu

Ten Centuries

Points: 1071
More actions
February 6, 2012 at 2:00 pm

#250289

I have a table >190M large with >420K records. The table grows ~1000 records a day.
I have a busy service application that sometimes deletes 1-5 records from the table on the fly.
The question is if this sort of deleting is too bad for the application performance that I should replace on the fly deleting with marking records and deleting in a daily or weekly job?

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Eliyahu (2/6/2012)
I have a table >190M large with >420K records. The table grows ~1000 records a day.
I have a busy service application that sometimes deletes 1-5 records from the table on the fly.
The question is if this sort of deleting is too bad for the application performance that I should replace on the fly deleting with marking records and deleting in a daily or weekly job?

It all depends... if the five deletes are done using the PK or any other unique index it will probably not hurt performance but if the delete statements are forcing a full table scan for each delete then you have a performance problem in your hands - what does the execution plan tells you?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jared SSC Guru Points: 61793 More actions · Answer 2

Can I ask WHY you think this may be a problem?

Jared
CE - Microsoft

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I would tend to shy away from that kind of approach. You will have add additional where checks to every single query IsDeleted = 0

Now you will need an additional index for data that should just be removed when it is no longer relevant. I don't think you will find any performance gains because now you are updating the record instead of deleting. I have never been a big fan of "soft deletes". This type of thing always seems to get nasty to maintain.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eliyahu Ten Centuries Points: 1071 More actions · Answer 4

Thanks for the answers. I was under impression that deleting is the most expensive operation. Apparently it is a concern only for large number of records to delete.