1

I have a table with records logs. Every few minutes I delete old logs by Service. Each deletion can have almost 10,000 records. The question is whether to delete all the logs with delete top, a few logs in loop or deleting them all at once will be more effective? I have indexes and there is no cascade on the tables.

  • 3
    Maybe this will help you : michaeljswart.com/2014/09/take-care-when-scripting-batches – Sabin Bio Nov 7 '18 at 9:15
  • Just a query, if 10,000 logs are being written and then deleted minutes later, is there a need to write them in the first place? Or are they being shipped off to be analysed before you delete them? – user122754 Nov 7 '18 at 10:23
  • 3
  • 1
    As @TomTom answered you, 10K is a tiny number to delete. Make sure you have an index in place that allows you to identify the ones you want to delete quickly and your transaction should be in and out before anything else notices. (assuming you don't have any LOB fields). Make sure you delete statement isn't doing a table scan and you should be fine. – Jonathan Fite Nov 7 '18 at 13:26
1

Generally:

10k records is tiny. I use loops in a script to bulk delete becasue I want to keep transactions smaller, but I do deltes of 64 million rows each loop.

Generally:

Loops of TOP X, outside a transaction, are more effective than deleting all rows if you do not care about the order of deletes. Reason simply is that every delete is then a separate transaction. Particularly in staging environments when you can for whatever reason not use truncate, this can be good to keep the tx log smaller. It also avoids longer locking because, again, smaller transactions.

But 10k rows is tiny to start with.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.