如何优化我的SQL递归查询以提升执行效率？

我平时其实不太待见递归查询，但这次要从末端/初始商机出发，回溯出完整的历史商机链，暂时没找到其他更合适的实现方式——而且最终这些结果还要传入另一个查询，生成每条商机链对应的单行数据。

第一步：基础数据准备（D1 CTE） #

这部分是用来获取后续操作需要的全部基础数据，你也可以考虑换成临时表或者表变量来存储这些信息：

WITH [D1] AS (
 SELECT [Opp]._Customer_ID [CusID], [Opp].Opportunity_ID [OppID], [Opp].Reference [Ref], [Opp].CoverStartDate_Actual [CoverStart], [Opp].Date_Cancelled [Cancelled], [Opp].Renewal, [Opp]._Product_ID [ProID], [Opp].inf_Opportunity_ID_Prior [PriorOppID], [Opp].Policy_Number [PolicyNo], [Opp].RealOpp [Real], [Opp].Deleted, [Opp].[Status], [Prior].Reference as PriorRef, DATEDIFF(MONTH,[Prior].CoverStartDate_Actual,[Opp].CoverStartDate_Actual) as PriorOppMonthsApart, [Prior].Months_Cover as PriorMonthsCover
 FROM dbo.crm_Opportunity [Opp]
 LEFT OUTER JOIN dbo.crm_Opportunity [Prior] ON [Prior].Opportunity_ID = [Opp].inf_Opportunity_ID_Prior
 WHERE ([Opp].Deleted = 0) AND ([Opp].[Status] = 5) AND ([Opp].RealOpp = 1)
 ),

第二步：递归回溯商机链（D2 CTE） #

欢迎进入"递归地狱"😂，正如我之前说的，我真的不喜欢用递归，但这是目前能实现商机链回溯的唯一办法——它会不断获取历史商机编号，直到追溯到没有前置商机（PriorOppID为NULL）的那条为止：

[D2] AS (
 SELECT [D1].OppID, [D1].Ref, [D1].CoverStart, [D1].Cancelled, [D1].Renewal, [D1].ProID, [D1].PriorOppID, [D1].PolicyNo, 1 AS Depth, [D1].Ref AS TopRef, CONVERT(VARCHAR(7),NULL) AS NextRef, [D1].PriorRef, [D1].PriorOppMonthsApart, [D1].PriorMonthsCover, [Cu].Introducer_Company_ID [Introducer], [D1].OppID AS _Opportunity_ID_Top
 FROM [D1]
 INNER JOIN dbo.crm_Customer [Cu] ON [D1].CusID = [Cu].Customer_ID
 WHERE ([Cu].Deleted = 0) AND ([Cu]._Company_ID = 'bd164825-a16a-4bee-9b01-6971604dda63') AND (
 SELECT COUNT(*) FROM ACTIVEQUOTE.dbo.crm_Opportunity op2
 WHERE ([D1].[PriorOppID] = [D1].OppID) AND ([D1].Deleted = 0) AND ([D1].[Status] IN (2,3,4,5))
 ) = 0
 UNION ALL
 SELECT [D1].OppID, [D1].[Ref], [D1].[CoverStart], [D1].[Cancelled], [D1].Renewal, [D1].ProID, [D1].PriorOppID, [D1].PolicyNo, [D2].Depth + 1 as Depth, [D2].TopRef, CONVERT(VARCHAR(7),[D2].Ref) as NextRef, [D1].PriorRef, [D1].PriorOppMonthsApart, [D1].PriorMonthsCover, [D2].[Introducer], [D2]._Opportunity_ID_Top
 FROM [D1]
 INNER JOIN [D2] ON [D1].OppID = [D2].PriorOppID
 ),

第三步：结果格式化输出（D3 CTE） #

最后这一步是把递归得到的数据整理成可用的格式，用PARTITION BY来判断销售周期的时长，然后查询出所有结果：

[D3] AS (
 SELECT [D2].[ProID], COUNT(*) OVER (PARTITION BY TopRef) AS Years, [D2].Depth, [D2].TopRef, [D2].Ref, [D2].NextRef, [D2].PriorRef, CASE WHEN [D2].[PriorRef] IS NULL THEN 1 ELSE 0 END AS OriginOpp, [D2].Renewal, [D2].OppID, [D2].CoverStart, [D2].Cancelled, [D2].PolicyNo, [D2].PriorOppMonthsApart, [D2].PriorMonthsCover, [D2].Introducer, [D2]._Opportunity_ID_Top
 FROM [D2]
 )
 SELECT * FROM [D3]

内容的提问来源于stack exchange，提问作者Micha