Update statement performance

  • 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/

  • 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

  • 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/

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply