November 1, 2009 at 8:16 pm
Hi - I had written a data correction script and I was told that this is one of the worst queries written as this would perform badly. I want to know if anyone else had any thoughts / suggestions
UPDATE Orders SET Orders.OrderTypeDescription =
CASE
WHEN AF.OrderId = 'A' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'X')
WHEN AF.OrderId = 'B' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'Y')
WHEN AF.OrderId = 'C' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'Z')
WHEN AF.OrderId = 'D' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'YY')
WHEN aF.OrderId = 'E' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'ZZ')
End
FROM Orders AF
INNER JOIN with <5> tables
WHERE
AND AF.OrderType in
(
'X',
'Y',
'Z',
'XX',
'YY',
'ZZ'
)
November 2, 2009 at 1:41 am
Being a correction query that you run only once, I wouldn't worry too much about it - as long as is doing its job.
Your concert should be to put constraints/foreign keys to avoid that thing to happen in the future.
November 2, 2009 at 1:50 am
(SELECT OrderTypeId FROM LookUp Where OrderType = 'X')
Will return a constant value , so why not use the constant ?
November 2, 2009 at 2:00 am
wat about this?
create table tempstore(id int identity (1,1),
OrderID nchar(2),
OrderType nchar(3))
insert into tempstore(OrderID,OrderType)
select 'A', 'X' union
select 'B','Y' union
select'C','Z' union
select'D','YY' union
select'E','ZZ'
UPDATE Orders
SET Orders.OrderTypeDescription = t.OrderType
FROM Orders AF join tempstore t
on
INNER JOIN with <5> tables
WHERE
u can only run the update statement, periodically, also you can eliminate the "where AF.OrderType in
(
'X',
'Y',
'Z',
'XX',
'YY',
'ZZ'
) "
as the table only has these values
November 2, 2009 at 5:35 am
Agree with Dave, lose the sub selects and use the direct value. one of a few options you could use
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 2, 2009 at 8:22 am
Thank you'll for your response.
2 good points, 1) being a data correction script how much we should worry about query being good performant 2) Use of constant values
November 4, 2009 at 8:15 am
Even though it may be a run-once query, performance should still be a consideration. If you are running this in a production environment, with millions of rows, and the query is a bad performing query - you could potentially create timeouts, connection problems, unhappy customers, etc.
This query is nowhere close to being as bad as many of the queries I have seen. However, you will see some performance gain by removing the subqueries as already mentioned.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply