SQL - Breaking the query

Question

I got this below query from my legacy application, while executing this, it is taking too much time to respond. So I am planning to break this complicated query into multiple queries and do some of the operations in the application side i.e. newer application.

 WITH members    AS (       
    SELECT
    shift_group_code,
    shift_code,
    0 AS isCycle,
    CHAR(10) + CAST(shift_group_code AS varchar(max)) + CHAR(10) AS [path]       
    FROM shift_grouping       
    WHERE workspace_code IN ('default', 'test', 'test1')       
    AND shift_group_code = 'WS+DEFAULT'       
UNION
ALL       
    SELECT
    H.shift_group_code,
    H.shift_code,
    CASE                  
        WHEN M.[path] LIKE '%' + CHAR(10) + CAST(H.shift_code AS varchar(max)) + CHAR(10) + '%'                  
        THEN 1                  
        ELSE 0              
    END AS isCycle,
    M.[path] + CAST(H.shift_code AS varchar(max)) + CHAR(10) AS [path]       
    FROM
    shift_grouping H       

    JOIN
    members M       
        ON H.shift_group_code = M.shift_code       
        AND M.shift_code       IN (SELECT
            code 
    FROM
        shift_group)       
    WHERE
    H.workspace_code       IN ('default', 'test', 'test1')       
    AND M.isCycle = 0)       

    SELECT
        *       
    FROM shift       
    WHERE
        workspace_code       IN (
            'default', 'test', 'test1'
        )       
        AND (
            code = 'WS+DEFAULT'            
            OR code IN (
                SELECT
                    DISTINCT shift_code                        
                FROM
                    members                         
                WHERE
                    isCycle = 0
            )
        )

I could able to break the first part before UNION

i.e.

SELECT shift_group_code, shift_code,
        0 AS isCycle,
        CHAR(10) + CAST(shift_group_code AS varchar(max)) + CHAR(10) AS [path]       
FROM shift_grouping       
WHERE workspace_code       IN ('default', 'test', 'test1')       
AND shift_group_code = 'WS+DEFAULT'

But the second portion of the query

SELECT H.shift_group_code, H.shift_code,
        CASE                  
            WHEN M.[path] LIKE '%' + CHAR(10) + CAST(H.shift_code AS varchar(max)) + CHAR(10) + '%'                  
            THEN 1                  
            ELSE 0              
        END AS isCycle,
        M.[path] + CAST(H.shift_code AS varchar(max)) + CHAR(10) AS [path]       
 FROM shift_grouping H       
 JOIN members M 
 ON H.shift_group_code = M.shift_code       
 AND M.shift_code IN (SELECT code FROM shift_group)       
 WHERE H.workspace_code IN ('default', 'test', 'test1')

I couldn't able to break, because there is a JOIN members M statement which is not allowing me to break the SQL.

members is coming from WITH members statement at the beginning of the bigger query.

Or I couldn't able to understand how are they joining members with shift_grouping.

Can someone able to provide some assistance to break this query for me?

Answer 1

Is difficult to optimize that query without more knowledge about your database schema, but it can help you to understand what it's doing.

I also replaced a couple of IN conditions to EXISTS, it will give you a quite better performance, but i don't think that will be enough

Members is a Common Table Expression (CTE) that defines a temporary result set, when it call itself is known as a Recursive CTE.

SHIFT_GROUPING seems to be your Hierarchy table so, the first SELECT statement defines where the CTE starts, the SELECT after the UNION ALL defines the iterations

In general, a recursive CTE has three parts:

1. An initial query that returns the base result set of the CTE. The initial query is called an anchor member.
2. A recursive query that references the common table expression, therefore, it is called the recursive member. The recursive member is union-ed with the anchor member using the UNION ALL operator.
3. A termination condition specified in the recursive member that terminates the execution of the recursive member.

The execution order of a recursive CTE is as follows:

• First, execute the anchor member to form the base result set (R0), use this result for the next iteration.
• Second, execute the recursive member with the input result set from the previous iteration (Ri-1) and return a sub-result set (Ri) until the termination condition is met.

• Third, combine all result sets R0, R1, … Rn using UNION ALL operator to produce the final result set.

   ;WITH members    AS --CTE Declaration    
   (   
      -- Defines the initial query of the CTE
       SELECT
       shift_group_code,
       shift_code,
       0 AS isCycle,
       CHAR(10) + CAST(shift_group_code AS varchar(max)) + CHAR(10) AS [path]       
       FROM shift_grouping       
       WHERE workspace_code IN ('default', 'test', 'test1')       
       AND shift_group_code = 'WS+DEFAULT'       
  
      UNION ALL       
  
      -- Recursive query that references [members]
      SELECT
       H.shift_group_code,
       H.shift_code,
       CASE                  
           WHEN M.[path] LIKE '%' + CHAR(10) + CAST(H.shift_code AS varchar(max)) + CHAR(10) + '%'                  
           THEN 1                  
           ELSE 0              
       END AS isCycle,
       M.[path] + CAST(H.shift_code AS varchar(max)) + CHAR(10) AS [path]       
       FROM
       shift_grouping H       
       JOIN
       members M ON H.shift_group_code = M.shift_code  -- In this case, the join acts as the termination condition           
       WHERE 
      H.workspace_code IN ('default', 'test', 'test1')  
      AND M.isCycle = 0
       AND EXISTS (SELECT 1 FROM shift_group WHERE code = M.shift_code)    
   )    
   SELECT *       
   FROM shift S    
   WHERE 
      workspace_code IN ('default', 'test', 'test1')   
      AND ( 
          code = 'WS+DEFAULT' 
          OR EXISTS (
                   SELECT 1                    
                   FROM
                       members                         
                   WHERE
                       isCycle = 0 AND shift_code = S.code
           )
       )