CASE statement slows down query drastically

  • You ever use DTA much?  Here is what it output as index recommendations.

  • Jackie Lowery - Wednesday, January 10, 2018 11:33 AM

    You ever use DTA much?  Here is what it output as index recommendations.

    Nope.  Its recommendations aren't always good,

  • Lynn Pettis - Wednesday, January 10, 2018 12:13 PM

    Jackie Lowery - Wednesday, January 10, 2018 11:33 AM

    You ever use DTA much?  Here is what it output as index recommendations.

    Nope.  Its recommendations aren't always good,

    And are occasionally quite bad.

    "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

  • -- Add column Docref to index ix_sent_01

    -- in addition to eliminating the key lookup, this will refresh statistics, which look stale

    -- Another new index for you

    CREATE INDEX ix_Stuff ON Trade (TP_PartID) INCLUDE (TP_Name)

    -- And a couple of query changes

    SELECT DISTINCT                               
     m.Sent_ID,                   
     m.TP_PartID,                           
     DocType,                               
     ISNULL(DocRef,'')     AS 'DocRef',     
     DateSent,                   
     DocStatus,                             
     m.ICN,                                 
     m.GCN,                                 
     --ISNULL(err.DataKey, '') ErrorID,       
     CASE                                                       
     WHEN ISNULL(t.TP_Name,'') = '' THEN ISNULL(p.TP_Name,'')
     ELSE ISNULL(t.TP_Name,'')                             
     END                  AS 'TP_Name',                        
     m.TCN                                                                                                                

    FROM [sent] m  
    --LEFT JOIN ErrorLog err
    -- ON CAST(m.Sent_ID AS nvarchar(50)) = err.DataKey
    -- AND ISNULL(err.DataType, '') IN ('', 'sent')
    LEFT JOIN EDIStdDocs std
     ON m.DocType = std.doc_id                                  
    LEFT JOIN (
     SELECT p.PartnerID, p.DGID, p.TP_PartID, t2.TP_Name,
      q = COUNT(*) OVER(PARTITION BY p.PartnerID, p.DGID)
     FROM [Partner] p
     LEFT JOIN Trade t2
      ON t2.TP_PartID = p.TP_PartID
    ) p  
     ON p.PartnerID = m.TP_PartID
     AND p.DGID = std.DGID
     AND p.q = 1
    LEFT JOIN Trade t
     ON t.TP_PartID = m.TP_PartID
    WHERE m.DateSent >= '20171201' AND m.DateSent < '20171202'
    ORDER BY m.Sent_ID DESC
    “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

  • ChrisM,

     We're going to move to the new version of our EDI software.  The engineers I've been working with are telling me the indexes we've been looking at are already implemented in the newest version, so you were definitely taking me down the right path.  Also, they've been testing your version of the query and i think they're going to implement it in the next update.

    Thanks for all the help .

Viewing 5 posts - 31 through 34 (of 34 total)

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