April 17, 2015 at 12:47 am
Hi all,
While working on a performance task I need to optimize the performance ofthe query which is like this :
UPDATE table1
SET col1 = 'aa'
WHERE col1 < (SELECT MAX(col2) FROM tabl2 WHERE col3 = col4)
OR col1 < (SELECT MAX(col1) FROM tabl3 WHERE col3 = col4)
I checked that bad performance is due to WHERE clause condition.
Isthere any other way to write this?
Sorry due to restriction I can't post original query and plan.
Thanks in advance..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2015 at 1:46 am
From which tables are col1, col2, col3 and col4? Your sample query is pretty confusing because you didn't use aliases and you didn't put the aliases before the column names.
So it's impossible to tell if the subqueries are correlated or not.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 17, 2015 at 1:53 am
Modified the query using Alias:
UPDATE table1 t1
SET t1.col1 = 'aa'
WHERE t1.col1 < (SELECT MAX(t2.col2) FROM tabl2 t2 WHERE t2.col3 = t1.col4)
OR t1.col1 < (SELECT MAX(t3.col1) FROM t3.tabl3 WHERE t3.col3 = t1.col4)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2015 at 7:13 am
Instead of MAX, try a TOP 1 with an ORDER BY.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2015 at 7:19 am
Grant Fritchey (4/17/2015)
Instead of MAX, try a TOP 1 with an ORDER BY.
Is it such a difference? I would expect that internally a MAX is implemented the same way.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 17, 2015 at 7:23 am
Favorite answer, it depends.
Check the execution plan. It might do TOP on its own. In which case, ignore me. But if it's doing aggregate functions there, modifying it could be a huge performance boost.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2015 at 4:16 pm
Code below could perform better, esp. if table2 and table3 don't have an index that directly supports the lookup/comparison being done.
Edit: And performance could be worse with this code ... can't be sure ahead of time without seeing table DDL, query plans, etc.. Although I wouldn't expect it to be much worse if it is.
UPDATE t1
SET col1 = 'aa'
FROM table1 t1
LEFT OUTER JOIN (
SELECT col3, MAX(col2) AS col2_max
FROM table2
GROUP BY col3
) AS t2 ON t1.col4 = t2.col3 AND t1.col1 < t2.col2_max
LEFT OUTER JOIN (
SELECT col3, MAX(col1) AS col1_max
FROM table3
GROUP BY col3
) AS t3 ON t2.col3 IS NULL AND t1.col4 = t3.col3 AND t1.col1 < t3.col1_max
WHERE t2.col3 IS NOT NULL
OR t3.col3 IS NOT NULL
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply