| Author |
Topic |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
 Posted - 2007-08-01 : 09:35:02
|
As an addition to the legendary "Twit List" where people are nominated for a longer period of outstanding work, I thought we could start a "bad coding" list. It has been on my mind for a long time now (I got a lot of code by email to review before).This is one of them. The Orders table held about 50 million records of which about 4 million of them had the status of Pending.DELETE OrdersWHERE OrderID IN (SELECT OrderID FROM Orders WHERE Status = 'Pending') AND Status <> 'Pending' The question was "How can I speed this up?"The answer I gave was "Remove the whole DELETE statement. It doesn't do anything except spend time.".I really tried hard to explain why it didn't do anything. He did not believe me.
E 12°55'05.25"N 56°04'39.16"EDIT: Column names and table names have been altered to protect the guilty one. |
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-01 : 09:51:42
|
| Can that be bad practice since it doesn't do anything? I suppose it gives the server a workout to prevent it getting soft round the middle. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-01 : 10:04:17
|
I can think the CPU spiked for a while when getting the pending orders.I'll be most happy if someone else post a better "bad practice".
E 12°55'05.25"N 56°04'39.16"EDIT: Change the topic header to "Bad coding". |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 10:23:07
|
"I can think the CPU spiked for a while when getting the pending orders"If this becomes a common occurrence the SQL Developers will optimise it out ...  |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-01 : 10:35:44
|
Hey that is a good way to load the CPU and SQL Server for stress test
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-01 : 10:39:07
|
And no need for BEGIN TRAN / ROLLBACK TRAN either!
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 10:40:15
|
| "And no need for BEGIN TRAN / ROLLBACK TRAN either!"Is the OP not planning to put a NOLOCK in the nested select then? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 11:16:27
|
| "I think almost any entry from the Trigger Madness thread will automatically qualify for this thread."Will there be a Thread merge feature in SQLTeam_2008? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-08-01 : 11:21:35
|
quote:
Originally posted by Peso
I really tried hard to explain why it didn't do anything. He did not believe me.
Assuming that OrderID is unique. Seriously, a poorly designed database might NOT have OrderID as physically or even logically unique, in which case the code you posted might have some actual effect.e4 d5 xd5 Nf6 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-01 : 11:47:27
|
Why would that matter?If Orders table are1 Dropped2 Pending3 Shipped1 Pending then the record "1 dropped" would be deleted, yes.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Bad coding