3
Answers

This query is very slow how to enhance it to be more faster?

ahmed elbarbary

ahmed elbarbary
3y
524
1

I work on sql server 2012 I face issue this query when run is very slow so how o enhance it
to be more faster

query and execution plan exist on link below :

https://www.brentozar.com/pastetheplan/?id=S1wEKwf5O

 
sql query as below :
  1. ;WITH cte AS  
  2. (  
  3.   
  4.    
  5.   
  6.   SELECT   
  7.     Po.GlobalPnId ,  
  8.                 Po.FamilyId,  
  9.                 po.CompanyID,  
  10.                 Po2.GroupId,  
  11.              
  12.                 CAST( CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN ''   
  13.                                   WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')  
  14.                                   ELSE Po.PortionKey END))  
  15.                             ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''   
  16.                                   WHEN Po2.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(Po2.PortionKey))))  
  17.                                   WHEN CHARINDEX('[', PO2.PortionKey) >0 then LTRIM(RTRIM(replace(PO2.PortionKey,N'[',N'[[')))  
  18.                                   ELSE Po2.PortionKey END)) )  
  19.                     AS NVARCHAR(200))PortionKey  
  20.                     ,  CAST( CONCAT(LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''   
  21.                                   WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')  
  22.                                   ELSE Po.PortionKey END))  
  23.                             ,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''   
  24.                                   WHEN PNK.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(PNK.PortionKey))))  
  25.                                   WHEN CHARINDEX('[', PNK.PortionKey) >0 then LTRIM(RTRIM(replace(PNK.PortionKey,N'[',N'[[')))  
  26.                                   ELSE PNK.PortionKey END)) )  
  27.                     AS NVARCHAR(200)) PartNumber  
  28.                        
  29.   
  30.   
  31.   FROM      
  32.   
  33.   extractreports.dbo.GetFinalResult Po WITH(NOLOCK)   
  34.                 INNER JOIN extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK) ON Po.GlobalPnId = Po2.GlobalPnId  And  Po.GroupId = 1 AND Po2.GroupId = 2  
  35.                 INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK) ON Po.GlobalPnId = PNK.GlobalPnId  And  Po.GroupId = 1 AND PNK.GroupId = 2  
  36.   
  37.         WHERE      
  38.                 RTRIM( Po.PortionKey) <> ''  AND RTRIM( Po2.PortionKey) <> ''  
  39.                 AND Po2.PortionKey NOT LIKE '%[_]'   
  40.                 and Po.companyid=@CompanyId  
  41.               
  42.  UNION ALL  
  43.     SELECT   
  44.      t.GlobalPnId ,  
  45.                 t.FamilyId,  
  46.                 t.CompanyID,  
  47.                 Po2.GroupId,  
  48.                  
  49.                 CAST(CONCAT(t.PortionKey  
  50.                             ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''    
  51.                                            
  52.                                             WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[')   
  53.                                             ELSE Po2.PortionKey End ))  
  54.                     )  AS NVARCHAR(200)) PortionKey  
  55.                       
  56.                     ,  CAST(CONCAT(t.PortionKey  
  57.                             ,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''    
  58.                                             
  59.                                             WHEN CHARINDEX('[', PNK.PortionKey) >0 then replace(PNK.PortionKey,N'[',N'[[')   
  60.                                             ELSE PNK.PortionKey End ))  
  61.                     )  AS NVARCHAR(200)) PartNumber  
  62.   
  63.                       
  64.                       
  65.   
  66.     FROM CTE t  
  67.      INNER JOIN  extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK) ON Po2.GlobalPnId = t.GlobalPnId  AND Po2.GroupId = t.GroupId+ 1  
  68.      INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK) ON PNK.GlobalPnId = t.GlobalPnId  AND PNK.GroupId = t.GroupId+ 1  
  69.      
  70.      WHERE t.companyid=@CompanyId   
  71.   
  72.                 AND RTRIM( t.PortionKey) <> ''  AND RTRIM( Po2.PortionKey) <> ''  
  73.                 
  74.                   
  75. )  
  76. select * ,(Select Max(GroupId) from cte c2 Where c2.FamilyId=c1.FamilyId ) MX into extractreports.dbo.getfinalmask from cte c1  
 

so how to enhance it to be more faster

it   too much time may be reach to one hour

and

script ddl and data dml
exist here below :
https://www.mediafire.com/file/hz74ca3z08xiic8/getscriptfinalresult.sql/file

Answers (3)
Next Recommended Forum