Recursive Select runs slow

Question

I have a recursive Select, but the data is not really hierarchical and contains loops. To prevent an endless loop I've added the trick to collect and check the path. But it's ultra slow, even for a table with just 94 entries is it running forever - the real table has thousands of entries. Any ideas to improve performance?

Update: I have a large amount of approvals and have to split them into several packages. Each package should contain all requests for a user and a request can have mutliple users as approver. It doesn't matter where to start.

Here's a dbFiddle https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d22f5c83ee6b3c8f960e0b5a29de8c8a

 WITH approvalpack (uniqueid, requestid, userid, chain) 
       AS (SELECT uniqueid, 
                  requestid, 
                  userid, 
                  Cast(uniqueid AS VARCHAR(max)) chain 
           FROM   approvals 
           WHERE  uniqueid = 1 
           UNION ALL 
           SELECT a1.uniqueid, 
                  a1.requestid, 
                  a1.userid, 
                  Cast(( chain + ',' + convert(varchar,a1.uniqueid) ) AS VARCHAR(max)) AS 
                  chain 
           FROM   approvals a1 
                  INNER JOIN approvalpack p1 
                          ON p1.userid = a1.userid
           WHERE  chain NOT LIKE '%,' + convert(varchar,a1.uniqueid) + ',%' 
                  AND chain NOT LIKE '%,' + convert(varchar,a1.uniqueid) + '%' 
                  AND chain NOT LIKE '%' + convert(varchar,a1.uniqueid) + ',%' 
                  AND chain <> convert(varchar,a1.uniqueid)
           UNION ALL 
           SELECT a1.uniqueid, 
                  a1.requestid, 
                  a1.userid, 
                  Cast(( chain + ',' + convert(varchar,a1.uniqueid) ) AS VARCHAR(max)) AS 
                  chain 
           FROM   approvals a1 
                  INNER JOIN approvalpack p1 
                          ON p1.requestid = a1.requestid 
           WHERE  chain NOT LIKE '%,' + convert(varchar,a1.uniqueid) + ',%' 
                  AND chain NOT LIKE '%,' + convert(varchar,a1.uniqueid) + '%' 
                  AND chain NOT LIKE '%' + convert(varchar,a1.uniqueid) + ',%' 
                  AND chain <> convert(varchar,a1.uniqueid)) 
  SELECT * FROM approvalpack

Answer 1

For such recursion, I approach this be defining the pairs of unique ids -- and then recurse over the pairs. That is, I eliminate the userid and requestid in the first step:

with pairs as (
      select distinct a1.uniqueid as uniqueid1, a2.uniqueid as uniqueid2
      from approvals a1 cross join
           approvals a2
      where a1.userid = a2.userid or a1.requestid = a2.requestid 
     ),
     cte as (
      select  uniqueid1, uniqueid2, 
             convert(varchar(max), concat(',', uniqueid1, ',', uniqueid2, ',')) as chain,
             1 as lev
      from pairs p
      union all
      select cte.uniqueid1, p.uniqueid2,
             concat(cte.chain, p.uniqueid2, ','), lev + 1
      from cte join
           pairs p
           on cte.uniqueid2 = p.uniqueid1
      where cte.chain not like concat('%,', p.uniqueid2, ',%') 
     )
select uniqueid1, min(uniqueid2)
from cte
group by uniqueid1

I figure you want a unique identifier in the end. So this returns one row per unique identifier.

Here is a db<>fiddle.