Post reply

One Hour Query that used to take 13 minutes

  • August 12, 2008 at 12:33 pm

    #856964

    Don't think teh UPPER has anything to do with it.

    If I comment out the joing:

    LEFT OUTER JOIN SW_ORD_LIST_EXT OLE ON (UPPER(BH.ORDER_NO) = UPPER(OLE.ORDER_NO) AND BDTL.LINE_NO = OLE.LINE_NO)

    The entire query runs in 4 seconds

  • August 12, 2008 at 12:35 pm

    #856965

    pino_daddy (8/12/2008)

    Don't think teh UPPER has anything to do with it.

    If I comment out the joing:

    LEFT OUTER JOIN SW_ORD_LIST_EXT OLE ON (UPPER(BH.ORDER_NO) = UPPER(OLE.ORDER_NO) AND BDTL.LINE_NO = OLE.LINE_NO)

    The entire query runs in 4 seconds

    And if you do the join as

    LEFT OUTER JOIN SW_ORD_LIST_EXT OLE ON (BH.ORDER_NO = OLE.ORDER_NO AND BDTL.LINE_NO = OLE.LINE_NO)

    ?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 12, 2008 at 12:39 pm

    #856968

    If your collation is CI_AS then there is no reason for the UPPER conversion in the joins, so I'd remove those.

    The only other comments I have, that I have not seen made yet, is:

    Do you really need all the joins to be LEFT OUTER JOINS? I don't really consider myself a performance expert, but I am pretty sure an INNER JOIN will outperform an OUTER JOIN and I see in the execution plan that 2 of your joins are being converted to INNER JOINS by the Optimizer. The one Barry mentioned and the join of the HEADER and DETAIL tables.

    I'd also evaluate all the functions and CASE statements to determine if they are all really necessary or if I could clean them up.

    Grant has mentioned the amount of data being moved/queried and I would also wonder why that much data has to be queried at once. Is there any way to limit the data?

    Something else no one has asked is what else is different on the servers beyond the SQL Server versions? Is the same amount of memory available the same? Are the disks the same? Is the load the same? What is your buffer cache hit/ratio? Are the statistics IO the same?

    Finally you mention that making the change Barry suggested to the join caused execution to go from 41 secs to 65 secs, that could easily be explained by needing to recompile the query plan. You may actually have a better plan using his suggestion.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 12, 2008 at 12:41 pm

    #856970

    pino_daddy (8/12/2008)

    Don't think teh UPPER has anything to do with it.

    If I comment out the joing:

    LEFT OUTER JOIN SW_ORD_LIST_EXT OLE ON (UPPER(BH.ORDER_NO) = UPPER(OLE.ORDER_NO) AND BDTL.LINE_NO = OLE.LINE_NO)

    The estimated and actual row counts are way different from each other on that join (estimated = 54.3, actual = 48490)

    That cardinality inaccuracy very likely has made the table spool far more expensive than the optimiser estimated and the nested loop possibly very, very inefficient.

    Can you do an UPDATE STATISTICS SW_ORD_LIST_EXT WITH FULLSCAN and see if that changes the plan at all or removes the inaccuracy. (along with taking out the unnecessary UPPER functions)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 12, 2008 at 1:03 pm

    #856983

    YOU HIT THE NAIL MRIGHT ON THE HEAD, Thank you so much.

    I removed the "UN-NEEDED" UPPER clauses and BINGO, query runs in 3 seconds. Returns the ssame number of rows and same exact data (52,664 rows). That was Great

    Thank you soooo much.

    I appreciate it very much.

  • August 12, 2008 at 1:21 pm

    #856997

    pino_daddy (8/12/2008)

    YOU HIT THE NAIL MRIGHT ON THE HEAD, Thank you so much.

    I removed the "UN-NEEDED" UPPER clauses and BINGO, query runs in 3 seconds. Returns the ssame number of rows and same exact data (52,664 rows). That was Great

    Thank you soooo much.

    I appreciate it very much.

    Heh. Glad we could help. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 12, 2008 at 1:31 pm

    #857002

    Don't you love happy endings???

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • August 12, 2008 at 10:03 pm

    #857154

    rbarryyoung (8/12/2008)

    I ask because those UPPER functions on your joins are likely killing any chance of using the indexes.

    Heh... good thing the OP ignored your original suggestion right up to the end, Barry... wouldn't have been quite as much fun, otherwise. 😛 Must be the funny hat you wear or something... go back to just plain tinfoil.:P:D:)

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

  • August 13, 2008 at 5:50 am

    #857313

    Jeff Moden (8/12/2008)

    Must be the funny hat you wear or something... go back to just plain tinfoil.:P:D:)

    Tinfoil does block the mind control transmissions better but just doesn't inspire the same respect from the troops. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 15, 2008 at 1:37 pm

    #858950

    You've probably already evaluated, but the use of a function in a join (UPPER in this case) negates the use of an index. Is your server set to be case sensitive? If not, you could remove 'UPPER' from the statement.

    Also, are these joins static? or ad-hoc? If the latter, then I'd review indexes on those columns, as well as 'BH.order_date'.

    JM2c...

    FROM SW_BOOKING_DETAIL BDTL

    LEFT OUTER JOIN SW_BOOKING_HEADER BH ON UPPER(BDTL.CTRL_NUM) = UPPER(BH.CTRL_NUM)

    LEFT OUTER JOIN SW_BOOKING_DETAIL_SEATS BDS ON ( UPPER(BDTL.CTRL_NUM) = UPPER(BDS.CTRL_NUM)

    AND BDTL.LINE_NO = BDS.LINE_NO

    )

    LEFT OUTER JOIN SW_ORD_LIST_EXT OLE ON ( UPPER(BH.ORDER_NO) = UPPER(OLE.ORDER_NO)

    AND BDTL.LINE_NO = OLE.LINE_NO

    )

    LEFT OUTER JOIN SW_Booking_Setup ON 1 = 1

    WHERE BH.order_date >= SW_Booking_Setup.CurrPeriodStart - 30

    Dr. Les Cardwell, DCS-DSS
    Enterprise Data Architect
    Central Lincoln PUD

Viewing 10 posts - 16 through 24 (of 24 total)

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