ntaylor (5/19/2008)

---

I prefer the mantra of one of my better managers: "If you're going to do something out of the ordinary then document it, in the code, very thoroughly and...

karthikeyan (5/19/2008)

---

Once I repair the error in the #2 code you provided, it gives me the following...

-----------------------------------------------------

3.0

(1 row(s) affected)

...

Heh.. "Oh no"... I've earned the nickname of the code I hate the most 😛

Glad it helped, Chris.

Do keep in mind that the others are correct, though... if you have...

...or 52,000...

Only thing left is to ask Karthik if he understands how it works and why... 😉

In that case, it may very well be that it's remembering a "bad" plan based on a previous query. That's why I use stored procedures instead of embedded SQL...

Philip Yale (5/19/2008)

---

Thanks again for the reply Jeff.

I didn't actually ask why the copy to the heap was faster. In fact, the copy takes roughly the same length of...

Michael Meierruth (5/18/2008)

---

First of all, the delete statement I posted has a slight problem. It works correctly for the duplicates and would delete the duplicates and leave only one row...

george sibbald (5/19/2008)

---

the results are in:

straight delete took 8 min 47 secs and created 4GB of tran log

crawler delete took 21:49 at batches of 100,000 (40MB of log) and 9:22...

Michael Meierruth (5/19/2008)

---

Jeff,

What do you make of this? George deleted 99.96% from a table holding 6.3 million rows - all in one go and in less than 9 minutes. Can...

Once I repair the error in the #2 code you provided, it gives me the following...

[font="Courier New"]-----------------------------------------------------

3.0

(1 row(s) affected)

...

You've obviously had to change the code to accomodate the new column name and the new table name... I'm pretty good, but without seeing the code, there's no way I...

Not compared to the alternative's for generating multiple rows from a single row.

First one is definitely "hidden RBAR"... it doesn't have an explicit loop, but it does have a correlated subquery and executes that correlated sub-query once for each row updated. ...

Heh... that's a...