CODEWITHSUNDEEP
sql-servert-sql
Burt
Burt

Reputation: 7758

Performance issue with CTE SQL Server query

We have a table with a parent child relationship, that represents a deep tree structure.

We are using a view with a CTE to query the data but the performance is poor (see code and execution plan below).

Is there any way we can improve the performance?

WITH cte (ParentJobTypeId, Id) AS 
( 
   SELECT   
       Id, Id 
   FROM     
       dbo.JobTypes 

   UNION ALL 

   SELECT   
       e.Id, cte.Id 
   FROM     
       cte 
   INNER JOIN 
       dbo.JobTypes AS e ON e.ParentJobTypeId = cte.ParentJobTypeId 
) 
SELECT  
    ISNULL(Id, 0) AS ParentJobTypeId,
    ISNULL(ParentJobTypeId, 0) AS Id
FROM    
    cte

CTE Execution Plan

Upvotes: 0

Views: 119

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

A quick example of using the range keys. As I mentioned before, hierarchies were 127K points and some sections where 15 levels deep

The cte Builds, let's assume the hier results will be will be stored in a table (indexed as well)

Declare @Table table(ID int,ParentID int,[Status] varchar(50))
Insert @Table values
(1,101,'Pending'),
(2,101,'Complete'),
(3,101,'Complete'),
(4,102,'Complete'),
(101,null,null),
(102,null,null)


;With cteOH (ID,ParentID,Lvl,Seq) 
 as (
     Select ID,ParentID,Lvl=1,cast(Format(ID,'000000') + '/' as varchar(500)) from @Table where ParentID is null 
     Union All
     Select h.ID,h.ParentID,cteOH.Lvl+1,Seq=cast(cteOH.Seq + Format(h.ID,'000000') + '/' as varchar(500)) From @Table h INNER JOIN cteOH ON  h.ParentID = cteOH.ID
    ),
    cteR1 as (Select ID,Seq,R1=Row_Number() over (Order by Seq) From cteOH),
    cteR2 as (Select A.ID,R2 = max(B.R1) From cteOH A Join cteR1 B on (B.Seq Like A.Seq+'%') Group By A.ID)
    Select B.R1
          ,C.R2
          ,A.Lvl
          ,A.ID
          ,A.ParentID 
    Into  #TempHier
    From  cteOH A 
    Join  cteR1 B on (A.ID=B.ID) 
    Join  cteR2 C on (A.ID=C.ID) 

    Select * from #TempHier

    Select H.R1
          ,H.R2
          ,H.Lvl
          ,H.ID
          ,H.ParentID
          ,Total    = count(*)
          ,Complete = sum(case when D.Status = 'Complete' then 1 else 0 end)
          ,Pending  = sum(case when D.Status = 'Pending'  then 1 else 0 end)
          ,PctCmpl  = format(sum(case when D.Status = 'Complete' then 1.0 else 0.0 end)/count(*),'##0.00%')
     From  #TempHier H
     Join  (Select _R1=B.R1,A.* From @Table A Join #TempHier B on A.ID=B.ID) D on D._R1 between H.R1 and H.R2
     Group By H.R1
          ,H.R2
          ,H.Lvl
          ,H.ID
          ,H.ParentID
     Order By 1

Returns the hier in a #Temp table for now. Notice the R1 and R2, I call these the range keys. Data (without recursion) can be selected and aggregated via these keys

R1  R2  Lvl ID  ParentID
1   4   1   101 NULL
2   2   2   1   101
3   3   2   2   101
4   4   2   3   101
5   6   1   102 NULL
6   6   2   4   102

VERY SIMPLE EXAMPLE: Illustrates the rolling the data up the hier. 

R1  R2  Lvl ID  ParentID    Total   Complete    Pending PctCmpl
1   4   1   101 NULL        4       2           1      50.00%
2   2   2   1   101         1       0           1      0.00%
3   3   2   2   101         1       1           0      100.00%
4   4   2   3   101         1       1           0      100.00%
5   6   1   102 NULL        2       1           0      50.00%
6   6   2   4   102         1       1           0      100.00%

The real beauty of the the range keys, is if you know an ID, you know where it exists (all descendants and ancestors).

Upvotes: 3

Related Questions