0

Its relatively fair SP but it consumes high CPU. I have optimised it to certain level and now I'm out of ideas.

What else can be looked at?

``` 
    DECLARE @Firm_ID_INT INT 
    DECLARE @TASK_ID_INT INT    

  SELECT distinct t.task_id AS id, t.client_id clientId,  fsc.code as TaskCategory,      

  (CASE WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) 
  like '%, ,' 
  THEN c.last_corporate_name    
  WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like 
  '%,' 
  THEN (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name )   
  ELSE (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name +','+ c.generation_type) END) 
   AS ClientName,   

  td.status, t.tasktype_id, tt.task_code AS TaskTypeCode, tt.task_name as taskTypeName, t.status_id 
  AS StatusID,   
 fr.status_name as StatusType,

--(SELECT TOP 1 status_name FROM dbo.firm_return WITH(NOLOCK) WHERE firm_id = t.firm_id AND status_id 
  = t.status_id)StatusType,    
  t.period_enddate AS PeriodEndDate, t.firm_id,  

  (SUBSTRING(um.first_name,1,1) +''+ SUBSTRING(um.middle_name,1,1) +''+ SUBSTRING(um.last_name,1,1)) 
  AS AssignedTo,  
   t.task_desc AS TaskDesc, c.account_number,  

        um.cUserFullName AS AssignedToName,  
          t.assigned_id, td.ext_assigned_to  

         FROM dbo.task_master AS t WITH(NOLOCK) 
         inner join dbo.firm_return fr ON fr.firm_id = t.firm_id AND fr.status_id = t.status_id
          INNER JOIN dbo.firm_task AS tt WITH(NOLOCK) ON  t.firm_id = tt.firm_id AND t.tasktype_id = tt.tasktype_id 
         --inner join firm_specific_category fsc ON fsc.firm_id=tt.firm_id   and fsc.category_id= tt.category_id
         INNER JOIN dbo.client_master AS c WITH(NOLOCK) ON t.firm_id = c.firm_id AND t.client_id = c.client_id   
        -- INNER JOIN dbo.firm_task AS tt WITH(NOLOCK) ON  t.firm_id = tt.firm_id AND t.tasktype_id = tt.tasktype_id   
         INNER JOIN dbo.Firm_specific_Category AS fsc WITH(NOLOCK) ON tt.firm_id = fsc.Firm_id AND tt.category_id=fsc.Category_id AND fsc.is_active=1   
         INNER JOIN dbo.task_duedate AS td WITH(NOLOCK) ON td.task_id = t.task_id AND td.FirmId = t.firm_id  
         INNER JOIN dbo.Linked_Tasks lt WITH(NOLOCK) ON t.firm_id = lt.firm_id AND t.task_id = lt.lnktask_id  
         LEFT OUTER JOIN dbo.user_master um WITH(NOLOCK) ON um.firm_id = t.firm_id AND um.user_id = t.assigned_id  
         WHERE T.FIRM_ID = @Firm_ID_INT AND t.task_id IN (@TASK_ID_INT) AND lt.islinked = 'Y'  

         UNION ALL   



         SELECT distinct t.task_id AS id, t.client_id clientId,  
          (SELECT   code FROM dbo.firm_specific_category fsc  WITH(NOLOCK) WHERE fsc.firm_id=tt.firm_id   
          and fsc.category_id=tt.category_id  )TaskCategory,  
          (CASE WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like '%, ,' THEN c.last_corporate_name    
          WHEN (c.last_corporate_name + ',' + c.first_name +' '+ c.middle_name +','+ c.generation_type) like '%,' THEN (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name )   
          ELSE (c.last_corporate_name + ', ' + c.first_name +' '+ c.middle_name +','+ c.generation_type) END) AS ClientName,   
          td.status, t.tasktype_id, tt.task_code AS TaskTypeCode, tt.task_name as taskTypeName, t.status_id AS StatusID,   
          (SELECT TOP 1 status_name FROM dbo.firm_return WITH(NOLOCK) WHERE firm_id = t.firm_id AND status_id = t.status_id)StatusType,  
          t.period_enddate AS PeriodEndDate, t.firm_id,  
          --(SELECT SUBSTRING(um.first_name,1,1) +''+ SUBSTRING(um.middle_name,1,1) +''+ SUBSTRING(um.last_name,1,1) AS Expr1 FROM dbo.user_master um WITH(NOLOCK) WHERE um.firm_id = t.firm_id AND um.user_id = t.assigned_id) AS AssignedTo,  
          (SUBSTRING(um.first_name,1,1) +''+ SUBSTRING(um.middle_name,1,1) +''+ SUBSTRING(um.last_name,1,1)) AS AssignedTo,  
          t.task_desc AS TaskDesc, c.account_number,  
          --(SELECT cUserFullName AS Expr1 FROM dbo.user_master AS um WITH(NOLOCK) WHERE firm_id = t.firm_id AND (user_id=t.assigned_id)) AS AssignedToName,  
          um.cUserFullName AS AssignedToName,  
          t.assigned_id, td.ext_assigned_to  

         FROM dbo.task_master AS t WITH(NOLOCK)   
         INNER JOIN dbo.client_master AS c WITH(NOLOCK) ON T.FIRM_ID = C.FIRM_ID AND t.client_id = c.client_id   
         INNER JOIN dbo.firm_task AS tt WITH(NOLOCK) ON t.firm_id = tt.firm_id AND t.tasktype_id = tt.tasktype_id   
         INNER JOIN dbo.Firm_specific_Category AS fsc WITH(NOLOCK) ON tt.firm_id = fsc.Firm_id AND tt.category_id=fsc.Category_id AND fsc.is_active=1    
         INNER JOIN dbo.task_duedate AS td WITH(NOLOCK) ON td.task_id = t.task_id AND td.FirmId = t.firm_id  
         INNER JOIN dbo.Linked_Tasks lt WITH(NOLOCK) ON t.firm_id = lt.firm_id AND t.task_id = lt.lnktask_id   
         LEFT OUTER JOIN dbo.user_master um WITH(NOLOCK) ON um.firm_id = t.firm_id AND um.user_id = t.assigned_id  
         WHERE T.FIRM_ID = @Firm_ID_INT AND lt.task_id IN (@TASK_ID_INT) AND lt.islinked='Y'   
         AND linkedtype IN (SELECT linkedtype FROM dbo.Linked_Tasks WITH(NOLOCK) WHERE firm_id = @Firm_ID_INT AND task_id IN (@TASK_ID_INT))   
New contributor
fabkhush is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Hard to make any concrete recommendations without the indexes and the execution plan but the part of the query that most alarms me is like '%, ,' Which will remove any chance of using an index, furthermore it looks like you could just test if the columns that you concatenate together are blank or null instead which ought to be much more efficient – Mo64 Jan 13 at 9:36
  • 2
    Upload the actual execution plan to Paste The Plan and add the link to your question. For starters, remove DISTINCT in the outer query unless required. Formatting is best done in app code but CONCAT and CONCAT_WS may perform better than the CASE expression. – Dan Guzman Jan 13 at 12:09
  • High CPU usage results of the use of functions and operators : CASE, concatenation (+), LIKE, SUBSTRING... Especially in the SELECT clause. A RDBMS is not a system intended to apply cosmetics. Discharge this work to the client application... – SQLpro Jan 13 at 13:18
  • Query Plan: brentozar.com/pastetheplan/?id=SyIrBKaAv – fabkhush Jan 14 at 8:48

Your Answer

fabkhush is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.