Delete

Question

Delete

Viewing 12 posts - 16 through 26 (of 26 total)

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

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...

The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeff Moden (6/12/2008)
Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...
The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?

True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.

If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.

😎

steveb. SSC-Forever Points: 46733 More actions · Answer 3

Could it be a case of your disks filling up with the transaction log?

have you checked for free space?

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 4

Lynn Pettis (6/12/2008)
Jeff Moden (6/12/2008)
Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...
The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?
True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.
If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.
😎

Correct, WITH (NOLOCK) only affects SELECTs.

I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.

--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)

noeld SSC Guru Points: 96590 More actions · Answer 5

Jeff Moden (6/12/2008)
Lynn Pettis (6/12/2008)
Jeff Moden (6/12/2008)
Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...
The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?
True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.
If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.
😎
Correct, WITH (NOLOCK) only affects SELECTs.
I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.

Sometimes there is no tracking in the amount of rows on tables and a sudden increase can be a "surprise" 😉

* Noel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden (6/12/2008)
Lynn Pettis (6/12/2008)
Jeff Moden (6/12/2008)
Not sure the use of a correlated subquery (a form of hidden RBAR) will speed anything up here... Maybe...
The real fact of the matter is the OP says it used to work just fine... and doesn't now... what could be the problem? Parallelism?
True enough Jeff. Looking back at the original post (and paraphrasing), the system is hanging and the factory floor can't work. The issue could be blocking. Pretty sure that the NOLOCK hint is ignored on the delete, and if the DELETE is going to delete 11,000,000 rows, I wouldn't be surprised if SQL puts a table lock on the table.
If it was working before but isn't now, the two things that come to my mind now would be data and disk fragmentation. The OP should check these out.
😎
Correct, WITH (NOLOCK) only affects SELECTs.
I just can believe the delete of 11 million rows didn't get caught blocking before. Somethings not quite right here. I think Lynn is on the right track... I've seen it where the undocumented ability to DELETE alias has bitten folks before. Gotta follow the rules and delete from a table name, instead.

Which track is right, the coding of the delete (I offered 2 solutions) or the fragmentation (disk and data)?

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Makes me wonder if it wouldn't be easier to just to the select...INTO on stuff you want to keep, blow out the original table and reinsert the items to keep.....

I don't recall hearing how many rows would be left after this little adventure.....

Either way - deleting that much in one chunk is bound to jam things up a bit. Perhaps deleting in smaller chunks would help, too.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Matt Miller (6/12/2008)
Makes me wonder if it wouldn't be easier to just to the select...INTO on stuff you want to keep, blow out the original table and reinsert the items to keep.....
I don't recall hearing how many rows would be left after this little adventure.....
Either way - deleting that much in one chunk is bound to jam things up a bit. Perhaps deleting in smaller chunks would help, too.

That is why I made the second code suggestion.

😎

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 9

Lynn Pettis (6/12/2008)
Which track is right, the coding of the delete (I offered 2 solutions) or the fragmentation (disk and data)?

I was talking about the DELETE code... but, yes, I believe both tracks are correct and both need to be considered. It may also be that someone made the log smaller and they got bit by unexpected growth during the delete. That would certainly "Hang" the system until the growth completed and the delete completed.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Lynn Pettis (6/12/2008)
Matt Miller (6/12/2008)
Makes me wonder if it wouldn't be easier to just to the select...INTO on stuff you want to keep, blow out the original table and reinsert the items to keep.....
I don't recall hearing how many rows would be left after this little adventure.....
Either way - deleting that much in one chunk is bound to jam things up a bit. Perhaps deleting in smaller chunks would help, too.
That is why I made the second code suggestion.
😎

yup - missed it! 😛

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Unfortunately, it doesn't look like the OP has been on SSC since yesterday morning. Hope he checks this thread and let's us know what's happening.

😎

noeld SSC Guru Points: 96590 More actions · Answer 12

Lynn Pettis (6/12/2008)
Unfortunately, it doesn't look like the OP has been on SSC since yesterday morning. Hope he checks this thread and let's us know what's happening.
😎

I think that happens more that we like 😉

* Noel