I have a large table with data like below.

The goal is to be able to retrieve the original ID and location associated with the original ID given any ID as input. For example, if I were to look for ID 2,4,9, the result should be

The question I have is if table @IDs is really large, the recursive CTE q I use will take too long to process (the actual table that I am applying this example for is more than a million lines). I would think that there is a way to filter table @IDs to contain just the relevant IDs related to my list @ORDERID but I don’t know how to do it. If I run this example on a real table, it will error out because it exceeds 100 recursion.

Here is the script I use:

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

I appreciate any help on this!