question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft edited

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

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 
     ------------
   ;WITH cte AS
 (
    
     
    
   SELECT 
     Po.GlobalPnId ,
                 Po.FamilyId,
                 po.CompanyID,
                 Po2.GroupId,
               
                 CAST( CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN '' 
                                   WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')
                                   ELSE Po.PortionKey END))
                             ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN '' 
                                   WHEN Po2.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(Po2.PortionKey))))
                                   WHEN CHARINDEX('[', PO2.PortionKey) >0 then LTRIM(RTRIM(replace(PO2.PortionKey,N'[',N'[[')))
                                   ELSE Po2.PortionKey END)) )
                     AS NVARCHAR(200))PortionKey
 ,  CAST( CONCAT(LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN '' 
                                   WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[')
                                   ELSE Po.PortionKey END))
                             ,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN '' 
                                   WHEN PNK.PortionKey LIKE '%[_]%' THEN SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(PNK.PortionKey))))
                                   WHEN CHARINDEX('[', PNK.PortionKey) >0 then LTRIM(RTRIM(replace(PNK.PortionKey,N'[',N'[[')))
                                   ELSE PNK.PortionKey END)) )
                     AS NVARCHAR(200)) PartNumber
                         
    
    
   FROM    
    
   extractreports.dbo.GetFinalResult Po WITH(NOLOCK) 
                 INNER JOIN extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK) ON Po.GlobalPnId = Po2.GlobalPnId  And  Po.GroupId = 1 AND Po2.GroupId = 2
 INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK) ON Po.GlobalPnId = PNK.GlobalPnId  And  Po.GroupId = 1 AND PNK.GroupId = 2
    
         WHERE    
 RTRIM( Po.PortionKey) <> ''  AND RTRIM( Po2.PortionKey) <> ''
                 AND Po2.PortionKey NOT LIKE '%[_]' 
 and Po.companyid=@CompanyId
                
  UNION ALL
     SELECT 
  t.GlobalPnId ,
                 t.FamilyId,
                 t.CompanyID,
                 Po2.GroupId,
                   
                 CAST(CONCAT(t.PortionKey
                             ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN ''  
                                             
                                             WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[') 
                                             ELSE Po2.PortionKey End ))
                     )  AS NVARCHAR(200)) PortionKey
    
 ,  CAST(CONCAT(t.PortionKey
                             ,LTRIM(RTRIM(CASE WHEN PNK.PortionKey=N'Blank' THEN ''  
                                              
                                             WHEN CHARINDEX('[', PNK.PortionKey) >0 then replace(PNK.PortionKey,N'[',N'[[') 
                                             ELSE PNK.PortionKey End ))
                     )  AS NVARCHAR(200)) PartNumber
    
    
    
    
     FROM CTE t
      INNER JOIN  extractreports.dbo.GetFinalResult Po2 WITH(NOLOCK) ON Po2.GlobalPnId = t.GlobalPnId  AND Po2.GroupId = t.GroupId+ 1
      INNER JOIN extractreports.dbo.GetFinalResult_K PNK WITH(NOLOCK) ON PNK.GlobalPnId = t.GlobalPnId  AND PNK.GroupId = t.GroupId+ 1
       
      WHERE t.companyid=@CompanyId 
    
                 AND RTRIM( t.PortionKey) <> ''  AND RTRIM( Po2.PortionKey) <> ''
                  
                    
 )
 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 take rows 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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ahmedsalah-1628

Recursive CTE queries do have a reliance on the unique parent/child keys in order to get the best performance. If this is not possible to achieve, then a WHILE loop is potentially a much more efficient approach to handling the recursive query.

Please refer to the following article:

Optimize Recursive CTE Query

I checked your execution plan and there is no unusually high overhead. In addition, you should probably create a non-clustered index on the conditional column after where or the conditional column after join, because all I see are table scans.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

Try this:

 ;WITH CTE_GetFinalResult AS (
     SELECT 
         GlobalPnId, 
         FamilyId, 
         CompanyID, 
         GroupId,
         LTRIM(RTRIM(CASE WHEN PortionKey = N'Blank' THEN '' WHEN CHARINDEX('[', PortionKey) > 0 THEN REPLACE(PortionKey, N'[', N'[[') ELSE PortionKey END)) AS PortionKeyPart1,
         LTRIM(RTRIM(CASE WHEN PortionKey = N'Blank' THEN '' WHEN PortionKey LIKE '%[_]%' THEN SUBSTRING(PortionKey, LEN(LTRIM(RTRIM(PortionKey)))+1, LEN(LTRIM(RTRIM(PortionKey)))) WHEN CHARINDEX('[', PortionKey) > 0 THEN LTRIM(RTRIM(REPLACE(PortionKey, N'[', N'[['))) ELSE PortionKey END)) AS PortionKeyPart2
     FROM extractreports.dbo.GetFinalResult WITH(NOLOCK)
     WHERE 1 = 1 
     AND RTRIM(PortionKey) <> ''
     --AND GroupId IN (1, 2)
 ),
 CTE_GetFinalResult_K AS (
     SELECT 
         GlobalPnId, 
         FamilyId, 
         CompanyID, 
         GroupId,
         LTRIM(RTRIM(CASE WHEN PortionKey = N'Blank' THEN '' WHEN CHARINDEX('[', PortionKey) > 0 THEN REPLACE(PortionKey, N'[', N'[[') ELSE PortionKey END)) AS PartNumberPart1,
         LTRIM(RTRIM(CASE WHEN PortionKey = N'Blank' THEN '' WHEN PortionKey LIKE '%[_]%' THEN SUBSTRING(PortionKey, LEN(LTRIM(RTRIM(PortionKey)))+1, LEN(LTRIM(RTRIM(PortionKey)))) WHEN CHARINDEX('[', PortionKey) > 0 THEN LTRIM(RTRIM(REPLACE(PortionKey, N'[', N'[['))) ELSE PortionKey END)) AS PartNumberPart2
     FROM extractreports.dbo.GetFinalResult_K WITH(NOLOCK)
     WHERE 1 = 1 
     --AND GroupId IN (1, 2)
 ),
 CTE AS (
     SELECT 
         PO.GlobalPnId, 
         PO.FamilyId, 
         PO.CompanyID, 
         PO2.GroupId,
         CAST(CONCAT(PO.PortionKeyPart1, PO2.PortionKeyPart2) AS NVARCHAR(200)) AS PortionKey,
         CAST(CONCAT(PNK.PartNumberPart1, PNK.PartNumberPart2) AS NVARCHAR(200)) AS PartNumber
     FROM 
         CTE_GetFinalResult AS PO WITH(NOLOCK) 
     INNER JOIN
         CTE_GetFinalResult AS PO2 WITH(NOLOCK) ON PO.GlobalPnId = PO2.GlobalPnId  And  PO.GroupId = 1 AND PO2.GroupId = 2
     INNER JOIN
         CTE_GetFinalResult_K AS PNK WITH(NOLOCK) ON PO.GlobalPnId = PNK.GlobalPnId  And  PO.GroupId = 1 AND PNK.GroupId = 2    
     WHERE    
         PO2.PortionKey NOT LIKE '%[_]' 
     AND PO.CompanyId = @CompanyId
    
     UNION ALL
    
     SELECT 
         PO.GlobalPnId, 
         PO.FamilyId, 
         PO.CompanyID, 
         PO2.GroupId,
         t.PortionKey,
         t.PartNumber
     FROM 
         CTE AS t
     INNER JOIN
         CTE_GetFinalResult AS PO2 ON PO2.GlobalPnId = t.GlobalPnId AND PO2.GroupId = t.GroupId + 1
     INNER JOIN
         CTE_GetFinalResult_K AS PNK ON PNK.GlobalPnId = t.GlobalPnId  AND PNK.GroupId = t.GroupId + 1
     WHERE
         1 = 1
     AND t.CompanyId = @CompanyId
 )
    
 SELECT *, (SELECT Max(GroupId) FROM CTE AS c2 WHERE c2.FamilyId = c1.FamilyId) AS MX 
 INTO extractreports.dbo.getfinalmask 
 FROM CTE AS c1;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ahmedsalah-1628 commented

I did not have the time to look into this in any matter of detail. But would suggest that you split of the query. You have a CTE with a UNION ALL over two complex queries. Insert the result of those queries into temp table, and run the CTE over the temp tables instead. Try to identify good indexes on temp table tables.

No, wait, that CTE is recursive! Oh, well. But you should insert the output of the recursive CTE into a temp table, since you are using it twice in the final query. (Which means that it will be computed twice.)

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you for reply
can you please tell me
how to do below

No, wait, that CTE is recursive! Oh, well. But you should insert the output of the recursive CTE into a temp table, since you are using it twice in the final query. (Which means that it will be computed twice.)

0 Votes 0 ·

can you please tell me

No, wait, that CTE is recursive! Oh, well. But you should insert the output of the recursive CTE into a temp table, since you are using it twice in the final query. (Which means that it will be computed twice.)

can you please show me this answer by coding please

0 Votes 0 ·

i test above solution code

it still take too much time
I make solution exactly as first answer

0 Votes 0 ·