Breaking infinite loop in recursive CTE - Databases

Jack Douglas replying to artaxerxe

without testing, my baseline assumption is that the CTE will perform better, because (a) it's probably going to be split out in to something like those operations you mention internally, and (b) it'll do so with fewer context switches and less overhead. The main reason I'd want the CTE is that it is a lot lett to maintain though!

Andriy M replying to artaxerxe

I'm not very good at estimating these things. To me personally, the recursive CTE option seems faster, but I can't put my finger on why exactly I think that, it's more of an intuition I guess

artaxerxe

@Andriy, @JackDouglas Let's suppose that I'm concerned with the performance aspect. Do you think that a loop, a temporary table (instead of the varchar variable which stores the previous IDs) and a cursor (instead of the current CTE) would make this more efficient?

Andriy M

Done, cheers guys

Andriy M

@artaxerxe @Jack No problem, will post it in a minute. Glad you find the suggestion useful

artaxerxe replying to Jack Douglas

I thank that's a good idea. @Andriy, posting this as answer would be great, I think. Thanks.

Jack Douglas

@artaxerxe should we ash @Andriy to post an answer? I think his solution is much more in depth that the answer I posted.

artaxerxe replying to Andriy M

DISTINCT is the way to go, for sure in this case. I'm trying to think of a more efficient way to retrieve data. Performance tuning like. But it seems to me that would be possible with iterators only.

Andriy M replying to artaxerxe

Not sure how easy it would be to do with a recursive CTE but I think you always have the option of using DISTINCT in the final SELECT

Andriy M replying to artaxerxe

Yes, I deliberately put it there for demo purposes, so that it always starts from the bottom level children.  Meant to make the output smaller but complete enough, showing only full hierarchies, from the very bottom to the very top.  But I understand now that that filter would exclude items that are in a loop of their own, like when A references B, B, C, and C, A, and those would reference nothing else, and nothing else would reference those. Didn't think it through properly, but yeah, the filter can be removed and the query should still work and not break.

artaxerxe

Do you think this would be possible with CTE in this scenario?

artaxerxe

I would like to block going down the tree if a parallel child has the same parent somewhere.

artaxerxe

1, 3; 1, 4 is repeated because of 7, 1 relation.

artaxerxe

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=185815adbeb9affe85a1782f3bcbd73a

artaxerxe

@Andriy @JackDouglas this is not very efficient in case I have multiple children which reference same parent somewhere in the hierarchy. Look at the example provided in this sql fiddle:

artaxerxe

childId NOT IN (SELECT parentId FROM My_Table)

artaxerxe replying to Andriy M

your suggestions works if I remove WHERE clause for base query:

Andriy M

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7ed2bb8747d3e556df1d04b5aadd1f23

Andriy M replying to artaxerxe

Jack's solution works when the starting point participates in the circular dependency that you are trying to handle.  In the more general case where such dependency happens further down the road, you just have to collect all the nodes visited into a list, and check every item against that list as you go. Here's one way (borrowing your setup and essentially building on Jack's query):

artaxerxe

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9167faf65244b8194464d1b556cb3fc5

artaxerxe replying to Jack Douglas

Here is my modified fiddle:

artaxerxe replying to Jack Douglas

I took a short vacation. I'll come back next week.

Jack Douglas replying to artaxerxe

can you post a amended fiddle with the case you mean and the error?

artaxerxe

for example, 5 could have had 3 as parent insted of 1 (at the 'circular case' in my example)

artaxerxe

@Jack, re: [your answer](#a1976), This case is ok if the loop is created with the root. It won't break the infinite recursivity if the loop is created with some other child above it and under root.

2 Answers