CASE statement slows down query drastically

• Jackie Lowery

  SSCommitted

  Points: 1886

  More actions

  January 8, 2018 at 11:09 am

  #334333

  If i comment out the case statement in this query, it takes less than  a second.  With the case statement, it takes almost a minute.  Any ideas?  Can the double join on the Trade table be written better than t1 or t2?

  
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 WITH (NOLOCK)       
                      

  LEFT JOIN ErrorLog err WITH (NOLOCK)        
 ON CAST(m.Sent_ID AS nvarchar(50)) = err.DataKey
AND ISNULL(err.DataType, '') IN ('', 'sent')  

                        
LEFT JOIN Trade  t WITH (NOLOCK)         
   ON m.TP_PartID = t.TP_PartID          
                      
LEFT JOIN EDIStdDocs std WITH (NOLOCK)         
   ON m.DocType = std.doc_id           
                      
LEFT JOIN Partner  p WITH (NOLOCK)          
   ON m.TP_PartID = p.PartnerID          
   AND std.DGID  = p.DGID            
   AND 1=(SELECT COUNT(TP_PartID)          
     FROM Partner             
     WHERE PartnerID = p.PartnerID        
      AND DGID  = p.DGID)         
                      
LEFT JOIN Trade  t2 WITH (NOLOCK)         
   ON p.TP_PartID = t2.TP_PartID          
                      
WHERE DateSent >= '12/1/2017' AND DateSent <= '12/1/2017 23:59:59'              
ORDER BY m.Sent_ID DESC


• Thom A

  SSC Guru

  Points: 99126

  More actions

  January 8, 2018 at 11:18 am

  #1974946

  What is your reason for using NOLOCK? You know the implications of it, right?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jackie Lowery

  SSCommitted

  Points: 1886

  More actions

  January 8, 2018 at 11:24 am

  #1974948

  This is a query that, unfortunately, I'm having to help the software developer figure out the problem with.  I tried removing the NOLOCKs but didn't seem to impact performance.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 8, 2018 at 1:43 pm

  #1974967

  Please post the actual execution plan as a .sqlplan file.

• Grant Fritchey

  SSC Guru

  Points: 398679

  More actions

  January 8, 2018 at 1:45 pm

  #1974968

  Have you looked at the execution plan with and without the CASE statement. If you want, post both (full .sqlplan file please, and if possible, the actual execution plan, not the estimated) and one, or more, of us will look through it to figure out what's going on.

  Just looking at the code, I'm surprised the CASE is giving you problems when this bit:
  CAST((mm..Sent_IDSent_ID AS nvarchar AS nvarchar((5050)))) == err err..DataKeyDataKey   
  AND ISNULL  AND ISNULL((errerr..DataTypeDataType,, '''')) IN IN (('''',, 'sent''sent')) 
Is absolutely going to lead to scans of that table. Functions against columns like you have them is very problematic.

  And if NOLOCK doesn't impact performance, chuck it.

  "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

• January 8, 2018 at 10:43 pm

  This was removed by the editor as SPAM

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  January 9, 2018 at 8:10 am

  #1975082

  The TP_Name column isn't used anywhere else in the query, so possibly something as simple as indexes that are covering without the CASE expression no longer are once that column is needed.

  Would it be possible to post the execution plans for the query with and without the CASE expression?

  That will help us give more pointed advice; as other have already pointed out, there are quite a few red flags in that query.

  Cheers!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 9, 2018 at 8:11 am

  #1975083

  Jackie Lowery - Monday, January 8, 2018 11:09 AM

  If i comment out the case statement in this query, it takes less than  a second.  With the case statement, it takes almost a minute.  Any ideas?  Can the double join on the Trade table be written better than t1 or t2?

  
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 WITH (NOLOCK)       
                      

  LEFT JOIN ErrorLog err WITH (NOLOCK)        
 ON CAST(m.Sent_ID AS nvarchar(50)) = err.DataKey
AND ISNULL(err.DataType, '') IN ('', 'sent')  

                        
LEFT JOIN Trade  t WITH (NOLOCK)         
   ON m.TP_PartID = t.TP_PartID          
                      
LEFT JOIN EDIStdDocs std WITH (NOLOCK)         
   ON m.DocType = std.doc_id           
                      
LEFT JOIN Partner  p WITH (NOLOCK)          
   ON m.TP_PartID = p.PartnerID          
   AND std.DGID  = p.DGID            
   AND 1=(SELECT COUNT(TP_PartID)          
     FROM Partner             
     WHERE PartnerID = p.PartnerID        
      AND DGID  = p.DGID)         
                      
LEFT JOIN Trade  t2 WITH (NOLOCK)         
   ON p.TP_PartID = t2.TP_PartID          
                      
WHERE DateSent >= '12/1/2017' AND DateSent <= '12/1/2017 23:59:59'              
ORDER BY m.Sent_ID DESC


  Commenting out the CASE eliminates columns from Partner p, Trade t and Trade t2 from the output. If SQL Server can determine that omission of these tables from the query doesn't change the output at all, including row count, then it can eliminate them from the query altogether. And of course with DISTINCT in the output, you've increased the likelihood of this occurring.
  There's some scope for improvement in this query, if that's what you're looking to do.

  ^{“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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 9, 2018 at 8:48 am

  #1975094

  And again, post the actual execution plans for the query with and without the CASE.  We need more information than you have provided and these will provide that information.  Be sure you upload the plans as *.sqlplans, not as pictures.

• Jackie Lowery

  SSCommitted

  Points: 1886

  More actions

  January 9, 2018 at 8:59 am

  #1975097

  Here are the execution plans with and without the case statement.  My ultimate goal would be to improve the query.  I think the issue lies in joining the Trade table twice in order to get the TP_Name based on the TP_PartID join between the Partner and Sent tables, if that makes sense.

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  January 9, 2018 at 9:09 am

  #1975098

  Yeah, since you no longer need the TP_Name column when the CASE expression is commented out, SQL Server doesn't even need to access any tables other than Sent and ErrorLog, and smartly ignores the rest of the joins, as Chris had surmised might be happening.

  When TP_Name is needed, then it has to actually implement all the joins, which is a lot more expensive.

  That explains the duration difference you're seeing; I'll take a closer look at rewriting the query later.

  Cheers!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 9, 2018 at 9:11 am

  #1975099

  Jackie Lowery - Tuesday, January 9, 2018 8:59 AM

  Here are the execution plans with and without the case statement.  My ultimate goal would be to improve the query.  I think the issue lies in joining the Trade table twice in order to get the TP_Name based on the TP_PartID join between the Partner and Sent tables, if that makes sense.

  Thanks.
  This could get you started:
  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(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 < '13/1/2017'    
           
  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

• Jackie Lowery

  SSCommitted

  Points: 1886

  More actions

  January 9, 2018 at 9:42 am

  #1975107

  The original query only takes 40 sec to complete, that query takes 56 sec.  Also, many of the returned records have an empty TP_Name field, and none of the TP_Name fields are empty with the original query.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 9, 2018 at 9:42 am

  #1975108

  Could we get the DDL for the tables including the indexes currently defined?

• Jackie Lowery

  SSCommitted

  Points: 1886

  More actions

  January 9, 2018 at 10:03 am

  #1975118

  Here is ddl for sent, trade, and partner tables, the ones with the joins that seem to cause issue.