本文介绍了带有选项的无限循环CTE(最大递归0)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有大量记录的CTE查询。以前它工作正常。但是最近,它对某些成员抛出了错误

I have CTE query with large record on it. Previously it worked fine. But lately, it throws an error for some members

所以我放了 OPTION(最大递归0) OPTION(maxrecursion 32767),因为我不想限制记录。但是,结果是查询需要永远加载。我该怎么解决?

So I put OPTION (maxrecursion 0) or OPTION (maxrecursion 32767) on my query, because I don't want to limit the records. But, the result is the query takes forever to load. How do I solve this?

这是我的代码:

with cte as(
-- Anchor member definition
    SELECT  e.SponsorMemberID , e.MemberID, 1 AS Level
    FROM tblMember AS e
    where e.memberid = @MemberID

union all

-- Recursive member definition
    select child.SponsorMemberID , child.MemberID, Level + 1
    from tblMember child

join cte parent

on parent.MemberID = child.SponsorMemberID
)
-- Select the CTE result
    Select distinct a.*
    from cte a
    option (maxrecursion 0)

编辑:易于理解的代码

已解决:因此,问题不是出自 maxrecursion 。来自CTE。我不知道为什么,但可能包含任何赞助者周期:A-> B-> C-> A-> ...(感谢@HABO)

SOLVED: So the issue is not came from maxrecursion. It's from the CTE. I don't know why but possibly it contain any sponsor cycles: A -> B -> C -> A -> ... (Thanks to @HABO)

我试过这种方法,它的工作原理。

I tried this method and it works. Infinite loop in CTE when parsing self-referencing table

推荐答案

如果您要达到递归限制,那么您在赞助关系或数据循环方面都具有相当大的深度。像下面这样的查询将检测循环并终止递归:

If you are hitting the recursion limit, you either have considerable depth in sponsoring relationships or a loop in the data. A query like the following will detect loops and terminate the recursion:

declare @tblMember as Table ( MemberId Int, SponsorMemberId Int );
insert into @tblMember ( MemberId, SponsorMemberId ) values
  ( 1, 2 ), ( 2, 3 ), ( 3, 5 ), ( 4, 5 ), ( 5, 1 ), ( 3, 3 );
declare @MemberId as Int = 3;
declare @False as Bit = 0, @True as Bit = 1;

with Children as (
  select MemberId, SponsorMemberId,
    Convert( VarChar(4096), '>' + Convert( VarChar(10), MemberId ) + '>' ) as Path, @False as Loop
    from @tblMember
    where MemberId = @MemberId
  union all
  select Child.MemberId, Child.SponsorMemberId,
    Convert( VarChar(4096), Path + Convert( VarChar(10), Child.MemberId ) + '>' ),
    case when CharIndex( '>' + Convert( VarChar(10), Child.MemberId ) + '>', Path ) = 0 then @False else @True end
    from @tblMember as Child inner join
      Children as Parent on Parent.MemberId = Child.SponsorMemberId
    where Parent.Loop = 0 )
  select *
    from Children
    option ( MaxRecursion 0 );

这篇关于带有选项的无限循环CTE(最大递归0)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-27 15:10