Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Deleting batches of rows with TOP Expand / Collapse
Author
Message
Posted Thursday, July 3, 2008 7:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 449, Visits: 1,858
Hi Ted,

please don't take the comments as punishment. I think that all comments/questions with regard to your article were to clarify any points that your target audience couldn't get from just reading the article.

..... but then again. If you enjoy punishment we can do that too :D



Regards,

WilliamD
Post #528056
Posted Thursday, July 3, 2008 8:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
Ted Pin (7/3/2008)
@Jeff:

Thanks for your feedback. I will try to create a more well thought-out article for my next submission based on the comments in this discussion. I looked around for an article that guides article writing, but couldn't find one. Would a prolific contributor like to write for us?


You mean the "Sexy Enough for Dinner Conversation?" site? No, I'll pass... I like to get all my "punishment" through a single source. :) But I very much appreciate the offer.

You seem to write a lot on that site, Ted... in fact, quite the writer you are. So, I'm curious... Why did you shortcut this article on "deletes" so badly?

Also, just to be sure... the intent of my comments was not to punish... they were meant to be suggestions. But, just like one of your articles said, "Because IT lacks an inherent human element,..." it is sometimes difficult to make a suggestion or comment on something without coming across that way. Sorry...


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #528092
Posted Thursday, July 3, 2008 8:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
david.gerrard (7/3/2008)
Hey- I've learned a whole lot from the discussion - more than I would have done if I'd had to skim read a much longer article.


I'm not sure I understand that... you'll read the discussions, the total of which is a dozen times longer than the article, and interact on some of those discussions... yet, you would only "skim read" a much longer article? Then, you may want to scan the "scripts" section of this forum... most everything there is about the same length as this "article".

Just to clarify... my main objection has nothing to do with the length of the article. My objection is because the code doesn't necessarily provide the "nice availability and logging benefits" stated in the first paragraph of the article and additional research before writing the article would have shown that.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #528122
Posted Thursday, July 3, 2008 8:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 1:33 PM
Points: 85, Visits: 299
Jeff Moden (7/3/2008)
Ted Pin (7/3/2008)[hrI looked around for an article that guides article writing, but couldn't find one. Would a prolific contributor like to write for us?


You mean the "Sexy Enough for Dinner Conversation?" site? No, I'll pass... I like to get all my "punishment" through a single source. :) But I very much appreciate the offer.

You seem to write a lot on that site, Ted... in fact, quite the writer you are. So, I'm curious... Why did you shortcut this article on "deletes" so badly?

Also, just to be sure... the intent of my comments was not to punish... they were meant to be suggestions. But, just like one of your articles said, "Because IT lacks an inherent human element,..." it is sometimes difficult to make a suggestion or comment on something without coming across that way. Sorry...


Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"

Thanks for looking at my blog. To answer your question, I'm not really sure why I missed the mark so badly. I suppose I've had a lot of practice writing editorial/essay-style articles but remain very ignorant of technical article standards. This I hope to rectify, thanks in no small part to your, and others', well-directed feedback.

GermanDBA, my wife thinks I am a stickler for punishment but maybe that's because it works to her advantage =D In all truth, I very much appreciate the comments made herein. I, like many "writers," continually learn to take criticism and try to improve. The response to this article has really been a wake up call for me and that can only be a good thing.


---------------------------
|Ted Pin >>
Post #528124
Posted Thursday, July 3, 2008 8:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 23,237, Visits: 31,929

The purpose of doing looped Deletes is usually two fold...
1. Do the deletes and still allow the table to be used.
2. Keep the transaction log small.

Item #2 above can only be accomplished if the DB is in the simple recovery mode or there is some additional code in the loop that truncates the log while it is running.



Going with #2, if the database is in Bulk-Logging or full recovery model, a transaction log backup is what really needs to be there to limit the growth of the t-log. Just wanted to expand on this (even though backup log with truncate only is being depreciated, someone may do that instead of actually backing up the log).




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #528147
Posted Thursday, July 3, 2008 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 12:06 PM
Points: 19, Visits: 196
The example was a bit confusing, mostly because "DELETE TOP(2)" threw me off, but this would be very useful in deleting large amount of data on a production system. I deal with this kind of thing regularly, for archiving tracking information. Keeping tables like this clean is essential in having a smoothly running mechanism with lightening fast inserts/updates.

What about using something like this? It may be a bit slower using the EXISTS clause, but it would still avoid many of the locking pitfalls of deleting large record sets simultaneously:

while exists (select col1 from tab1) delete top(2) from tab1

Post #528150
Posted Thursday, July 3, 2008 9:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 3:48 AM
Points: 17, Visits: 63
I meant that (as a relative begginer - certainly in terms of Database Admin if not design) it's actually easier to be introduced to a topic quickly at a high level than have to work through a long article that's packed with detail, where I would probably not have had the time to take all the finer points in.

As I'm not a DBA I hadn't even considered the issue of having to delete millions of rows at once. (I'm the person whos buggy code fills the table with rubbish, and haven't had to go thru the pain of taking it all out again... :)) However - today I've not only learned that there's a real performance benefit to deleting in batches, but I've also learned a great number of the finer points of actually doing so because of the discussion all you more knowlegeable guys have had afterwards.

In my latest job, we've potentially got a lot of cleansing work to do, so now I can make sure your best practices are brought to the table when we do it.
Post #528159
Posted Thursday, July 3, 2008 9:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
Ted Pin (7/3/2008)
Oops, I meant "Would a prolific contributor like to write [a guide to writing articles] for [the SQLServerCentral.com community]?"


Nah... I'd get it wrong... I think I need to find a couple of URLs to point folks to on how to write technical articles.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #528162
Posted Thursday, July 3, 2008 9:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 266, Visits: 2,585
Jeff: You comments about using the wait has me thinking. (Always dangerous.) I understood why one would break up a delete of a lot of rows, but I didn't think one would need a 'wait' to let other statement 'get in'.

Here's how I would think it would work: There is a que of requests to get things done and the requests are taken one at a time. The delete statements are adding requests very quickly, but if any other requests come in during that time, they get added to the que and would run in their turn, making other delete requests wait their turn.

So, why would a 'wait' statement really help anything? If there is say a 10 second delay between each delete statement and no other requests come in during that time, then it is 10 seconds of wasted time. And if another request had come in during say delete iteration #2, even if delete iteration #3 pops up right away, the other request would run between iteration #2 and #3. I would think.

I really don't know how it works. (Please don't anyone take the above statements as a description of the way SQL Server works.) I'm just trying to explain why your recommendation to use a wait command is confusing to me. It doesn't fit with my understanding of how SQL Server works.

Thanks,
- JJ
Post #528169
Posted Thursday, July 3, 2008 9:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
JJ B (7/3/2008)
Jeff: You comments about using the wait has me thinking. (Always dangerous.) I understood why one would break up a delete of a lot of rows, but I didn't think one would need a 'wait' to let other statement 'get in'.

Here's how I would think it would work: There is a que of requests to get things done and the requests are taken one at a time. The delete statements are adding requests very quickly, but if any other requests come in during that time, they get added to the que and would run in their turn, making other delete requests wait their turn.

So, why would a 'wait' statement really help anything? If there is say a 10 second delay between each delete statement and no other requests come in during that time, then it is 10 seconds of wasted time. And if another request had come in during say delete iteration #2, even if delete iteration #3 pops up right away, the other request would run between iteration #2 and #3. I would think.

I really don't know how it works. (Please don't anyone take the above statements as a description of the way SQL Server works.) I'm just trying to explain why your recommendation to use a wait command is confusing to me. It doesn't fit with my understanding of how SQL Server works.

Thanks,
- JJ


Good points, Jennifer (I got the name right, didn't I?)... you could run sp_locks to see if anyone else had locks on the table to see if you want to wait the delay. But, normally, people make the deletes large enough and the table is in a condition to lock the whole table during the delete. The delay keeps the delete from becoming rather exclusive. Like anything else, "it depends".


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #528177
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse