CODEWITHSUNDEEP
sqlsql-serverrecursioncommon-table-expressiondatabase-performance
morteza zakeri
morteza zakeri

Reputation: 11

Problem with Recursive CTE very long query plan

When I execute below query SQL run this plan and it took a long time to run it and it will not be over.

QueryPlanLink

I have 3 million records in #T table.

myCode:

;WITH cte1 AS (
    SELECT NationalId,len(NationalId) as LenNationalId,CustomerType,FullDateInt,time,
         SUM(Price) as SUMPrice
        ,AVG(Price) as Price
        ,SUM(Volume) as  Volume
        ,SUM (sum([Volume])) OVER (PARTITION BY NationalId,len(NationalId) ORDER BY FullDateInt,[Time]) as SumVol
        ,ROW_NUMBER() OVER (PARTITION BY NationalId,len(NationalId) ORDER BY FullDateInt,[Time]) AS rn
from #T as T1
group by NationalId,len(NationalId),CustomerType,FullDateInt,time

), rcte AS (
    SELECT *, Price AS Cost , cast(0 as decimal) as Profit
    FROM cte1 AS base
    WHERE base.rn = 1
    UNION ALL
    SELECT curr.*, Case when curr.Volume>0 Then ((curr.Volume *curr.Price) + (prev.Cost*prev.SumVol))/nullif(curr.SumVol,0) 
                        when curr.Volume<0 Then prev.Cost
                   End
    as Cost
    ,ISNULL(Cast (Case when curr.Volume<0 Then -1*(curr.Price-Cost)*curr.Volume End as decimal),0) as Profit
   
   FROM cte1 AS curr
  INNER  JOIN rcte AS prev 
    ON curr.NationalId = prev.NationalId  AND curr.rn = prev.rn + 1
)

Select * from rcte
option (maxrecursion 0)

Is there any way to make it better?

Thanks

Upvotes: 0

Views: 355

Answers (1)

morteza zakeri
morteza zakeri

Reputation: 11

I Change My Query like below And Everything is Done. Thanks For All.

SELECT NationalId,len(NationalId) as LenNationalId,CustomerType,FullDateInt,time,
         SUM(Price) as SUMPrice
        ,AVG(Price) as Price
        ,SUM(Volume) as  Volume
        ,SUM (sum([Volume])) OVER (PARTITION BY NationalId,len(NationalId) ORDER BY FullDateInt,[Time]) as SumVol
        ,ROW_NUMBER() OVER (PARTITION BY NationalId,len(NationalId) ORDER BY FullDateInt,[Time]) AS rn
into #TCTE from #T as T1
group by NationalId,len(NationalId),CustomerType,FullDateInt,time



;With rcte AS (
    SELECT *, Price AS Cost , cast(0 as decimal) as Profit
    FROM #TCTE AS base
    WHERE base.rn = 1
    UNION ALL
    SELECT curr.*, Case when curr.Volume>0 Then ((curr.Volume *curr.Price) + (prev.Cost*prev.SumVol))/nullif(curr.SumVol,0) 
                        when curr.Volume<0 Then prev.Cost
                   End
    as Cost
    ,ISNULL(Cast (Case when curr.Volume<0 Then -1*(curr.Price-Cost)*curr.Volume End as decimal),0) as Profit
   
   FROM #TCTE AS curr
  INNER  JOIN rcte AS prev 
    ON curr.NationalId = prev.NationalId  AND curr.rn = prev.rn + 1
)

Select * 
into #TFinal from rcte
option (maxrecursion 0)

Upvotes: 1

Related Questions