Post reply

CASE statement slows down query drastically

  • January 9, 2018 at 12:22 pm

    #1975148

    ChrisM@Work - Your query works great now.  Pretty sure i found the issue.  I did have to change 'COUNT(TP_PartID)' to 'COUNT(p.TP_PartID)' to fix an ambiguous name error.
    The main issue though, is the 'AND p.DGID = std.DGID' part b/c the DGID field in the std table is not indexed.
    If i remove that part, the original query takes about 98 seconds.  If I remove that part from your query, results are returned in 8 sec, so a massive improvement.  Here is the final query:


    SELECT DISTINCT    
     m.Sent_ID,  
     m.TP_PartID,   
     DocType,    
     ISNULL(DocRef,'') AS 'DocRef',
     DateSent,  
     DocStatus,   
     m.ICN,    
     m.GCN,    
     ISNULL(err.DataKey, '') ErrorID, 
     [TP_Name] = ISNULL(x.[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

    OUTER APPLY (
    SELECT [TP_Name]
    FROM (
    SELECT
    [TP_Name] = CASE WHEN t.TP_Name > '' THEN t.TP_Name ELSE t2.TP_Name END,
    q = COUNT(p.TP_PartID) OVER(PARTITION BY p.PartnerID, p.DGID)
    FROM [Partner] p
    LEFT JOIN Trade t
    ON t.TP_PartID = p.PartnerID
    LEFT JOIN Trade t2
    ON t2.TP_PartID = p.TP_PartID
    WHERE p.PartnerID = m.TP_PartID
    --AND p.DGID = std.DGID
    ) d
    WHERE d.q = 1
    ) x

    WHERE DateSent >= '12/1/2017' AND DateSent <= '12/31/2017 23:59:59'

    ORDER BY m.Sent_ID DESC

    Thanks for all the help guys.

  • January 9, 2018 at 2:52 pm

    #1975180

    I guess i should ask what i can do to be able to add the 'AND p.DGID = std.DGID' back to the query.  Do i need to add an index to the DGID column?  Does it need to be a certain type?

  • January 10, 2018 at 1:20 am

    #1975228

    Jackie Lowery - Tuesday, January 9, 2018 2:52 PM

    I guess i should ask what i can do to be able to add the 'AND p.DGID = std.DGID' back to the query.  Do i need to add an index to the DGID column?  Does it need to be a certain type?

    -- An index like this will support both the join and the aggregate (PARTITION BY).

    -- Modify an existing index if possible

    CREATE INDEX ix_Stuff ON [Partner] (PartnerID, DGID) INCLUDE (TP_PartID)

    “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

  • January 10, 2018 at 6:49 am

    #1975263

    I wouldn't need an index on the EDIStdDocs DGID column?

  • January 10, 2018 at 6:55 am

    #1975264

    Jackie Lowery - Wednesday, January 10, 2018 6:49 AM

    I wouldn't need an index on the EDIStdDocs DGID column?

    Not necessarily. This new index I've suggested will radically change the execution plan. Try it, post up an actual plan and folks can assess for you whether or not further changes will bring more benefit.

    “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

  • January 10, 2018 at 7:06 am

    #1975268

    This is the only index/key on the table now.  It seems to already contain the DGID column.


    ALTER TABLE [dbo].[Partner] ADD CONSTRAINT [PK_Partner] PRIMARY KEY CLUSTERED
    (
        [TP_PartID] ASC,
        [DGID] ASC,
        [PartnerQual] ASC,
        [PartnerID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

  • January 10, 2018 at 7:12 am

    #1975270

    Jackie Lowery - Wednesday, January 10, 2018 7:06 AM

    This is the only index/key on the table now.  It seems to already contain the DGID column.


    ALTER TABLE [dbo].[Partner] ADD CONSTRAINT [PK_Partner] PRIMARY KEY CLUSTERED
    (
        [TP_PartID] ASC,
        [DGID] ASC,
        [PartnerQual] ASC,
        [PartnerID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    Yes that's correct, but they're not in the right order for it to be useful for the query in the way that I anticipate.

    CREATE INDEX ix_Stuff ON [Partner] (PartnerID, DGID) INCLUDE (TP_PartID)
    I want only  PartnerID    , DGID as keys and preferably (but not necessarily) in that order. TP_PartID isn't necessary as a key. As the first key in the PK, it renders the PK unusable for the index seeks which this query needs.

    “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

  • January 10, 2018 at 7:34 am

    #1975279

    That index gives me a marked improvement.  Query time goes from 1:07 to 0:29.  But something still doesn't seem right b/c if i remove the 'AND p.DGID = std.DGID' part, the query only takes 3 sec.

  • January 10, 2018 at 7:36 am

    #1975281

    Jackie Lowery - Wednesday, January 10, 2018 7:34 AM

    That index gives me a marked improvement.  Query time goes from 1:07 to 0:29.  But something still doesn't seem right b/c if i remove the 'AND p.DGID = std.DGID' part, the query only takes 3 sec.

    Can you attach an "actual" plan please Jackie?

    “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

  • January 10, 2018 at 7:41 am

    #1975284

    Took me a sec to figure out how to get that. lol. I'm definitely a SQL server novice. Thanks for all the help.

  • January 10, 2018 at 8:15 am

    #1975292

    Jackie Lowery - Wednesday, January 10, 2018 7:41 AM

    Took me a sec to figure out how to get that. lol. I'm definitely a SQL server novice. Thanks for all the help.

    Thanks...

    -- Create this index:

    CREATE INDEX ix_doc_id ON EDIStdDocs (doc_id) INCLUDE (DGID)

    -- Try this variant of your query

    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(t2.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 PartnerID, DGID, TP_PartID,

    q = COUNT(*) OVER(PARTITION BY PartnerID, DGID)

    FROM [Partner]

    ) p

    ON m.TP_PartID = p.PartnerID

    AND std.DGID = p.DGID

    AND q = 1

    LEFT JOIN Trade t

    ON t.TP_PartID = m.TP_PartID

    LEFT JOIN Trade t2

    ON t2.TP_PartID = p.TP_PartID

    WHERE DateSent >= '12/1/2017' AND DateSent < '12/1/2017 23:59:59' -- is this Jan 12 or Dec 01?

    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

  • January 10, 2018 at 9:10 am

    #1975312

    Created the index and ran the query.  Took 42 sec.  The date is Dec 01.

  • January 10, 2018 at 9:11 am

    #1975313

    Is that index dependent on the previous index you gave me?  I've been removing them between tests.

  • January 10, 2018 at 10:04 am

    #1975341

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

    Is that index dependent on the previous index you gave me?  I've been removing them between tests.

    Best to leave all new indexes in place whilst testing, but ensure they have memorable names. The query would work better with new indexes on all of the tables but you have to weigh the performance improvement against their maintenance cost.

    “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

  • January 10, 2018 at 10:59 am

    #1975348

    The query completes in 46 sec. with both indexes, which is weird b/c it completed in 42 sec with only the second index you gave me. Hmmm.  Ive attached the actual execution plan with both indexes.

Viewing 15 posts - 16 through 30 (of 34 total)

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