Post reply

Need advice on optimizing a query

  • March 16, 2022 at 9:53 am

    #4000458

    Hi All,

    We have an application internal query which runs multiple times and it is taking ~30 mins. The query is selecting data from a VIEW.

    Wanted to know the causes of slowness of the query and looking for any possible recommendations.

    This database is vendor specific database and the query is generated/executed by that application itself. We haven't done the database design.

    SQL Server version is Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64).

    Looking for some direction/recommendations here so that we can get to the vendor and work with them.

    Uploading a zip file [data collection.zip] which has below information.

    1. Main SELECT query which is running slow as per app team. “1-Main query.txt”

    2. Query output results screenshot. “2-query output.png”

    3. View definition. “3-view definition.sql” and “2-view object type.JPG”

    4. 1st table involved in the join inside view definition “4-Table1 structure.sql” and “4-Indexes on Table1.png”

    5. 2nd table involved in the join of view definition. Refer “5-Table2 structure.sql” and “5-Table2 indexes.png”

    6. Table rowcounts “8-Rowcounts of each table.sql”

    7. Estimated plan “9-EstimatedPlan.sqlplan”

    8. Actual execution plan “10-ActualExecutionPlan.sqlplan”

    9. Set statistics IO AND TIME ON output “2-set statistics ouput.sql”

    Thanks in advance.

    Sam

     

     

     

     

    Attachments:
    You must be logged in to view attached files.

  • March 16, 2022 at 12:55 pm

    #4000537

    I'm not going to try to unpack all of that.

    The optimizer thinks that the filter on  C_MT_B_COMM_TO_PTY.PARTY_TYP_CD + ':' = 'Contact' + ':' is going to do a lot more than it actually does. These kinds of calculations run against columns can be very problematic. Fixing it requires digging into the code & structures to understand what the heck is going on, becuase, surely, if the above is true, then C_MT_B_COMM_TO_PTY.PARTY_TYP_CD = 'Contact' is true, right? So you don't need that calculation at all. That alone should help.

    Otherwise, all those IS NOT NULL filters... may not be doing anything at all. Is the majority of the data NOT NULL? I can actually suggest that it is. The table cardinality for SVR1_WTFR4W is 10,551,100. The rows returned, with all the NOT NULL predicates, is 10,522,177. That means you're only filtering 28,923 rows out of 10 million. Indexes will not help here. Filtered indexes won't help either. We're just not filtering data.

    Basically, from what you've provided, how many values in PARTY_TYP_CD are equal to the value 'Contact'? If it's the vast majority of them, as it seems to be, your only hope for speeding this up is hardware. Bigger, faster, more, hardware. If you're on the cloud, go to a much higher service tier.

    Otherwise, you need to look at something that actually filters data. Moving 10 million rows around isn't fast. Anywhere. For anyone. This is why, usually, we try to avoid that except when forced.

    "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

  • March 16, 2022 at 2:35 pm

    #4000606

    Heh... lordy.  Here's the rowcount you posted for the query in question...

    (10343197 row(s) affected)

    And here's the WHERE clause from the code you posted...

      WHERE C_MT_B_COMM_TO_PTY.PARTY_TYP_CD + ':' = 'Contact' + ':'
        AND C_MT_B_COMM_TO_PTY.EXACT_COMM_VALUE IS NOT NULL
        AND C_MT_B_COMM_TO_PTY.EXACT_COMM_TYPE IS NOT NULL
        AND C_MT_B_COMM_TO_PTY.PARTY_TYP_CD IS NOT NULL
        AND C_MT_B_COMM_TO_PTY.PARTY_ID IS NOT NULL

    Do you see any possible SARGability problems there???

    --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)

  • March 16, 2022 at 2:41 pm

    #4000612

    p.s. Nice content on the ZIP file.  The Actual Execution plan shows the result of the NON-SARGable query perfectly... a HASH MATCH to join a 5 million row table with a 10 million row table.

    --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)

  • March 17, 2022 at 6:15 am

    #4001097

    As Grant said, there a few rows which are getting filtered out. Index creation of no use in this case.

    Will ask them to work with the vendor.

    Thank you Grant and Jeff for quick inputs.

Viewing 5 posts - 1 through 4 (of 4 total)

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