Query performance change

  • The following query was executing within 1 minutes for the past 2 years without any issues.

    update

    dbo.INT_AS_VENTE_TITRE

    set

    P_STATUS = 'R',

    P_ERROR_CODE = 'E000026'

    from

    Interface a

    INNER LOOP JOIN AnotherDb.dbo.Trans b ON

    (convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and

    a.TXN_DT = b.TXN_DT)

    where

    a.P_STATUS <> 'R'

    and b.TICKET_NUM is not null

    and b.TXN_DT is not null

    OPTION (maxdop 0)

    Since last month, the query has been taking more than an hour everyday, with CPU load at 99-100%. Trans contains in excess of 200million lines and Interface contains around 200 000 lines.

    The Trans table has had its clustered index rebuilt, and statistics are regularly updated. I created an additional index with row_id, txn_dt and ticket_num after running tuning advisor, but the statement is still having same behaviour, even after a dbcc freeproccache.

    I am running out of ideas.

    Please suggest reasons as to why this statement could have changed behaviour so much, and how I could resolve this issue.

    Thanks

  • It would help if you posted the DDL for the tables, with indexes and the actual execution plan of the update. Without those, the information you get back might be limited to suggestions which may not hit the mark.


    And then again, I might be wrong ...
    David Webb

  • twoeyed (10/23/2012)


    The following query was executing within 1 minutes for the past 2 years without any issues.

    update

    dbo.INT_AS_VENTE_TITRE

    set

    P_STATUS = 'R',

    P_ERROR_CODE = 'E000026'

    from

    Interface a

    INNER LOOP JOIN AnotherDb.dbo.Trans b ON

    (convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and

    a.TXN_DT = b.TXN_DT)

    where

    a.P_STATUS <> 'R'

    and b.TICKET_NUM is not null

    and b.TXN_DT is not null

    OPTION (maxdop 0)

    Since last month, the query has been taking more than an hour everyday, with CPU load at 99-100%. Trans contains in excess of 200million lines and Interface contains around 200 000 lines.

    The Trans table has had its clustered index rebuilt, and statistics are regularly updated. I created an additional index with row_id, txn_dt and ticket_num after running tuning advisor, but the statement is still having same behaviour, even after a dbcc freeproccache.

    I am running out of ideas.

    Please suggest reasons as to why this statement could have changed behaviour so much, and how I could resolve this issue.

    Thanks

    I've seen this and similar hundreds of times. If you look carefully in Books Online, you'll never find an UPDATE there that looks like the one you have. You're updating 1 table from 2 other tables with no join between the table being updated and the source tables. Basically, you have a CROSS JOIN that SQL Server forgave up until the data got big enough to drive it nuts.

    The target table of the update ABSOLUTELY MUST be in the FROM clause and properly joined to the other table to work. I wouldn't be suprised to see your times drop to just a couple of seconds or less if you were to write the update as I suggested.

    And get rid of that LOOP join hint... it's part of the reason you weren't able to catch this error earlier.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff's nailed it:

    -- If *this* query returns any rows

    SELECT 1

    from Interface a

    INNER JOIN AnotherDb.dbo.Trans b

    ON (convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and a.TXN_DT = b.TXN_DT)

    where a.P_STATUS <> 'R'

    -- and b.TICKET_NUM is not null

    -- and b.TXN_DT is not null

    OPTION (maxdop 0)

    -- Then *this* unrestricted update will run, affecting every row

    Update dbo.INT_AS_VENTE_TITRE

    set P_STATUS = 'R', P_ERROR_CODE = 'E000026'

    - which is probably not the intended behaviour.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I will also note you are joining on mis-matched datatypes (or doing an unnecessary convert), which is also suboptimal.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • This observation may or may not be helpful.

    I couldn't help but notice your WHERE clause:

    "WHERE

    a.P_STATUS <> 'R'

    and b.TICKET_NUM is not null

    and b.TXN_DT is not null"

    All NOT comparisons cause scans instead of seeks. If you can find some way to avoid performing NOT comparisons, it would likely improve the performance of your query, if you have properly implemented supporting indexes. To what degree will depend on the sizes of your tables.

  • Thank you all for taking the time to reply and for your valuable inputs.

    I have managed to reduce the execution to around 6 minutes through the ideas proposed, which is not bad at all.

    Thank you Chris and David for the posting advice.

  • twoeyed (10/26/2012)


    Thank you all for taking the time to reply and for your valuable inputs.

    I have managed to reduce the execution to around 6 minutes through the ideas proposed, which is not bad at all.

    Thank you Chris and David for the posting advice.

    Hi

    It's good practice to post your final solution - it provides useful feedback to those who may have helped you, may help others who stumble upon this thread, and in some cases encourages another round of improvement.

    Cheers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Lee Crain (10/25/2012)


    This observation may or may not be helpful.

    I couldn't help but notice your WHERE clause:

    "WHERE

    a.P_STATUS <> 'R'

    and b.TICKET_NUM is not null

    and b.TXN_DT is not null"

    All NOT comparisons cause scans instead of seeks. If you can find some way to avoid performing NOT comparisons, it would likely improve the performance of your query, if you have properly implemented supporting indexes. To what degree will depend on the sizes of your tables.

    This may have been the case at one time, I do not believe this is the case any longer. It was once argued to use NOT EXISTS, but that would only matter if NULLS are considered. In some simple tests, I am getting the same query plan for a IN (criteria) as a NOT IN (criteria) on a 4 million row table. I think the optimizer in 2005 and up is doing an better job than the old version with regards to this.

Viewing 9 posts - 1 through 8 (of 8 total)

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