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

Question

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

Answer 1

Hi @ahmed salah ，

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.

Answer 2

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;

Answer 3

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.)