递归CTE性能优化及ID分离度字段实现问题（大表场景）

现有大型数据表，核心字段包含ID、PreviousID（关联父级ID，根节点为null）、Location等。需求为：输入任意ID，检索其对应的原始ID（即根节点ID）及原始ID关联的位置，同时计算ID与原始ID的分离度（根节点分离度为0，每向上追溯一层分离度加1，比如ID=2分离度0，ID=4分离度2，ID=9分离度1）。

一、已解决的分离度计算问题 #

该问题已由@ValNik解答，以下是简化实现脚本：

DECLARE @IDs TABLE (
  ID INTEGER
  ,PreviousID INTEGER
  ,Location INTEGER
)

INSERT INTO @IDs
SELECT           2,null,1235
UNION ALL SELECT 3,2,1236
UNION ALL SELECT 4,3,1239
UNION ALL SELECT 8,null,1237
UNION ALL SELECT 9,8,1234
UNION ALL SELECT 10,9,1235

Select * from @IDs


DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('4')
,('9')
,('2')

;WITH q AS (
    SELECT  ID, PreviousID,Location
    FROM    @IDs
    where ID in (select OrderID from @ORDERID) 
       -- or PreviousID in (select OrderID from @ORDERID)
    UNION ALL 
    SELECT  q.ID, u.PreviousID,q.Location
    FROM    q
            INNER JOIN @IDs u ON u.ID = q.PreviousID
            --and q.ID in (select OrderID from @ORDERID)
)


,CTE_Original as
(
SELECT  q.ID
        ,q.Location
        ,case when Min(PreviousID) is null then ID
                else min(PreviousID) end as OriginalID

FROM    q
GROUP BY q.ID,q.Location
)

Select CTE_Original.*,Original.Location as OriginalLocation from CTE_Original 
left join @IDs Original on Original.ID = CTE_Original.OriginalID
                        
where CTE_Original.ID in (select OrderID from @ORDERID)
order by ID

二、当前性能优化需求 #

实际场景中，上述示例的@IDs对应临时表#CTE_ORDERID，该表由多表关联生成，包含300万+行数据，生成插入耗时约20秒，但递归CTEq执行耗时超过10分钟。在无法查看执行计划的情况下，如何优化性能？

实际脚本如下：

DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('119309645')
,('115821862')
,('112942594')

;


Drop table if exists #CTE_OrderID

;
;WITH CTE_OrderID AS (
    SELECT ORDER_MED.ORDER_MED_ID as CURRENT_ORDERID
        ,ORDER_MED.CHNG_ORDER_MED_ID as PREV_ORDERID
        ,CLARITY_DEP.DEPARTMENT_NAME    as WRITTEN_LOCATION
        ,ORDER_MED.ORDERING_DATE        as WRITTEN_DATE
    FROM Clarity_PRD_Report.dbo.ORDER_MED (nolock)  
        LEFT JOIN Clarity_PRD_Report.dbo.CLARITY_DEP (nolock) on ORDER_MED.PAT_LOC_ID = CLARITY_DEP.DEPARTMENT_ID
    GROUP BY ORDER_MED_ID --,PAT.PAT_MRN_ID
        ,ORDER_MED.CHNG_ORDER_MED_ID
        ,CLARITY_DEP.DEPARTMENT_NAME    
        ,ORDERING_DATE  
        )
Select * 
Into #CTE_OrderID
From CTE_OrderID
;
With q as (
    Select CURRENT_ORDERID
           ,PREV_ORDERID
           ,PREV_ORDERID as Last_ORDERID
           ,WRITTEN_LOCATION
           ,WRITTEN_LOCATION as ORIGINAL_WRITTEN_LOCATION
           ,WRITTEN_DATE
           ,WRITTEN_DATE as ORIGINAL_WRITTEN_DATE
           ,0 NumOfTimesRewrite
           
           
    From #CTE_OrderID
        where CURRENT_ORDERID in (Select OrderID from @ORDERID) 

    UNION ALL 
    SELECT  q.CURRENT_ORDERID
            ,u.PREV_ORDERID
            ,q.PREV_ORDERID LAST_ORDERID
            ,q.WRITTEN_LOCATION
            ,u.WRITTEN_LOCATION
            ,q.WRITTEN_DATE
            ,u.WRITTEN_DATE
            ,NumOfTimesRewrite+1

    FROM    q
            inner JOIN #CTE_OrderID u ON u.CURRENT_ORDERID = q.PREV_ORDERID
            --and q.ORDER_MED_ID in (select orderID from @ORDERID)
)

Select CURRENT_ORDERID
    ,Coalesce(LAST_ORDERID,CURRENT_ORDERID) as ORIGINAL_ORDERID
    ,WRITTEN_LOCATION
    ,ORIGINAL_WRITTEN_LOCATION
    ,WRITTEN_DATE
    ,ORIGINAL_WRITTEN_DATE
    ,NumOfTimesRewrite

FROM q
WHERE PREV_ORDERID is null
order by CURRENT_ORDERID

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