Recursive SQL query to identify

Question

Please see the DDL below:

CREATE TABLE #MasterChild (MasterID decimal(25,0), ChildID decimal(25,0), ChildCreTime datetime)
insert into #MasterChild values (150021032000000173536533,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173571072,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173573651,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173574917,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173582487,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173604342,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173931636,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173935066,150021032000000173946207,'2016-04-22 13:27:30.483')
insert into #MasterChild values (150021032000000173946207,150021032000000173952172,'2016-04-22 13:38:32.747')
insert into #MasterChild values (150021032000000173946207,150021032000000173954415,'2016-04-22 13:43:28.120')
insert into #MasterChild values (150021032000000173536533,150021032000000173954321,'2016-04-22 13:43:28.120')
insert into #MasterChild values (150021032000000173954321,150021032000000173954319,'2016-04-22 13:43:28.120')

and the SQL below:

with GetAllMerges
as
(
select masterid,childid from #MasterChild where masterid=150021032000000173571072 and childid=150021032000000173946207
union all
select #MasterChild.masterid,#MasterChild.childid from #MasterChild inner join GetAllMerges
on  
#MasterChild.childid=GetAllMerges.masterid 
or (#MasterChild.childid=GetAllMerges.childid and #MasterChild.masterid<>GetAllMerges.masterid)  
) 
select distinct masterid,childid from GetAllMerges

I am seeing the following error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Why is this? I am trying to show all the childs and masters that are linked. For example, if master 1 is linked to child 2 and child 2 is linked to master 3 and master 3 is linked to child 4 and child 4 is linked to master 5, then persons 1-5 are linked (all childs and masters are persons).

score 3 · Answer 1 · answered Apr 28 '16 at 16:17

Recursive CTE's as you are attempting to do here have an implied MAXRECURSION of 100 before they error out.

You can specify how many recursions you want using the OPTION (MAXRECURSION n) WHERE n is a number between 0 and 32767, with 0 basically meaning run until compeletion no matter how long it takes.

I WOULD NOT ADVISE THIS IN YOUR CASE

Your CTE does not appear to have a termination for it's recursive element

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement.

(Emphasis added)

Re-write your query with a proper termination of the recursive CTE first before you go changing the MAXRECURSION settings.

In most cases - when a recursive CTE reaches the default of 100 and therefore breaks it is not a question of `MAXRECURSION` but rather a question of wrong traversal code :-) Another common problem is, if the last child is related to the parent in a way that the CTE is running in circles... — Shnugo, Apr 28 '16 at 16:31

score 0 · Answer 2 · answered Apr 28 '16 at 16:21

0

Let me see if I understand your method:

You're writing (GetAllMerges) to display the data you want, and INSIDE the function you refer back to it recursively?

==> #MasterChild.childid=GetAllMerges.masterid <===

I think you just set up an infinite loop.

I'm not sure why you aren't just using a while or do-while to step through the linked keys?

Also, you could and should use aliases when your table names are this long.

answered Apr 28 '16 at 16:21

LadyLovelace

1
1

1

*I'm not sure why you aren't just using a while or do-while to step through the linked keys?* Just because cursors, loops and step by step approaches are something one should avoid whereever possible? Read about RBAR, row-based and set-based ... A recursive CTE is a great tool to travers through hierarchical structures... – Shnugo Apr 28 '16 at 16:29
1

@Shnugo although apparently a recursive CTE is a [hidden RBAR](http://www.sqlservercentral.com/articles/T-SQL/74118/) – Shaneis Apr 28 '16 at 16:37
@Shaneis, I've gone through this article - great one, if you are looking for a counter, but not really related to this... It is clear, that a pure counter, adding +1 each step, will be RBAR at its best. But a hierarchy will deal with sets in each step. If there are 10 children below one parentID you will get all 10 children at once and so forth... Traversing a hierarchy (classically done with many cascading joins) is a heavy process in any case... And last but not least: A rCTE is **ad-hoc** SQL and therefore fully inlined (usage of indexes, stats,,,). – Shnugo Apr 28 '16 at 19:12
@Shnugo A recursive CTE is always processed RBAR. Rows are added to a [stack spool](https://blogs.msdn.microsoft.com/craigfr/2007/10/25/recursive-ctes/) and then always processed one by one with a nested loops in the recursive part. Rolling your own iterative queries that process a level at a time in a set based way can be much more efficient as it allows hash and merge join to be used too. – Martin Smith Apr 28 '16 at 19:17

score 0 · Answer 3 · answered Apr 28 '16 at 16:27

0

You should switch childid and parentid in your 'inner join' like this:

Try it out

with GetAllMerges
as
(
select masterid,childid,1 AS RecLevel 
from #MasterChild 
where masterid=150021032000000173571072 and childid=150021032000000173946207
union all
select #MasterChild.masterid,#MasterChild.childid,GetAllMerges.RecLevel + 1 
from #MasterChild 
inner join GetAllMerges on #MasterChild.masterid=GetAllMerges.ChildID 
--or (#MasterChild.childid=GetAllMerges.childid and #MasterChild.masterid<>GetAllMerges.masterid)  
) 
select distinct masterid,childid from GetAllMerges

I commented the or (#MasterChild... away, as this produces the error... Please describe what you are trying to achieve...

answered Apr 28 '16 at 16:27

Shnugo

66,100
9
53
114

Thanks, however, I need to also return the ones were the childid is a mastered on another row. Therefore the line of code you have commented needs to be uncommented. Uncommenting the line causes the original problem. – w0051977 Apr 28 '16 at 16:32
@w0051977 If I understand you correctly this would be something you should not include into the recursive CTE. Just add a join to the outer select and include these rows. You might `DISTINCT` this to avoid doublettes – Shnugo Apr 28 '16 at 16:34
I do not believe I can do this because the outer join would be recursive. For example, master 1 links to child 2, child 2 links to master 3, master 3 links to child 4 etc. – w0051977 Apr 28 '16 at 16:39
2

@w0051977 It would be of great help if you set up a minimal test scenario, with shorter IDs and with expected output fitting to your data. It is very dangerous to build a hierarchy over more than one column and more than one rule... Please use the edit option to put this into your question and give some details what you want to achieve... – Shnugo Apr 28 '16 at 16:51
my problem is the same as this one: http://stackoverflow.com/questions/14518090/recursive-query-in-sql-server – w0051977 Apr 29 '16 at 15:25

Recursive SQL query to identify

3 Answers3