Post reply

Query taking long time to execute

  • July 30, 2009 at 2:39 pm

    #207523

    SELECT Column Names

    FROM

    table-name ABL INNER JOIN

    table-name MAS ON

    MAS.[SOKey]=ABL.[SOKey] INNER JOIN

    table-name ILSS ON

    ABL.[ShipTranNo] = SUBSTRING(ILSS.SHIPMENT_ID,1,LEN(ABL.[ShipTranNo]))

    Above query is taking long time to execute. I think its because of last line.

    Any suggestions for improving performance of above query?

  • July 30, 2009 at 2:47 pm

    #1032692

    What does the execution plan look like?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 30, 2009 at 3:03 pm

    #1032700

    I looked at execution plan and everything looks good but Nested Loop (Inner Join) costs 90%.

  • July 30, 2009 at 3:28 pm

    #1032716

    Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

  • July 31, 2009 at 6:01 am

    #1032874

    You must be getting a scan because you're not going to see an index seek when you have functions on columns like that. Like Gail says, post the code, structure, sample data & actual execution plan (not estimated).

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 3, 2009 at 7:05 am

    #1033621

    1>.

    Make a computed column (with persisted) named "ShipTranNo"on

    the third table "ILSS"

    Alter Table ILSS

    Add

    ShipTranNo as LEFT(SHIPMENT_ID,) PERSISTED

    2>.

    Now create an index on this column "ShipTranNo"

    Create Index Idx_ILSS_ShiptranNo ON ILSS(ShipTranNo)

    3>. Now rewrite the Query as

    SELECT Column Names

    FROM

    table-name ABL

    INNER JOIN

    table-name MAS

    ON

    MAS.[SOKey]=ABL.[SOKey]

    INNER JOIN

    table-name ILSS

    ON

    ABL.[ShipTranNo] = ILSS.[ShipTranNo]

    This would work for you...

  • August 3, 2009 at 10:14 am

    #1033765

    Does using ILSS.SHIPMENT_ID like ABL.[ShipTranNo] + '%' make any difference?

    David

  • August 4, 2009 at 9:06 am

    #1034329

    Sanjays probably has the right solution here, assuming the number of rows matched is (or may be) low. Otherwise, you can force the plan to use a HASH join with the OPTION hint if the joins return more than a few percent of the total rows in the tables. Hmm, actually, Sanjays' solution should cover that too, because the index on the computed column would allow the optimizer to get an accurate estimate of rowcount and pick a hash join itself when appropriate.

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

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

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