递归 CTE 很长查询计划的问题

Question

当我执行下面的查询 SQL 时，运行这个计划，花了很长时间来运行它，而且它不会结束。

QueryPlanLink

我在 #T 表中有 300 万条记录。

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) 

有什么办法可以让它变得更好吗？

谢谢

Answer 1

我像下面这样更改我的查询，一切都完成了。感谢大家。

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)