| 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 Orders
WHERE 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 are
1 Dropped
2 Pending
3 Shipped
1 Pending
then the record "1 dropped" would be deleted, yes.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
|
Bad coding