Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My CTE runs in an infinite loop for a specific customer and I am not able to find out why.

Here is the query:

;WITH ClassTree
           AS (SELECT ID, NAME, Parent_ID
               FROM   TableName
               WHERE  ID = 1
               UNION ALL
               SELECT T.ID, T.NAME, T.Parent_ID
               FROM   TableName T WITH (NOLOCK)
                      JOIN ClassTree
                        ON Parent_ID = ClassTree.ID
)
SELECT * FROM ClassTree
share|improve this question
3  
If it does work for some customers, then for those customers it doesn't work for you likely have a hierarchy loop in the data. –  Nick Chammas Apr 5 '12 at 11:15
    
@NickChammas.. That is what i like to know how to find the –  Ashish Apr 5 '12 at 11:27
    
You would find that as your ID is joined to a Parent_ID, and somewhere in that chain the Parent_ID is joined to a same ID. That would be the cause of the loop. –  Thomas Stringer Apr 5 '12 at 12:46
    
What is the ID for Parent_ID = 1? –  Lamak Apr 5 '12 at 13:10
    
To avoid infinite loops, you may want to use HierarchyID or materialized path to implement your hierarchy. Your approach is very error-prone, as you have just seen. –  AlexKuznetsov Apr 5 '12 at 18:27

4 Answers 4

up vote 2 down vote accepted

I think this should find the problem record for you. It adds a LEVEL to your hierarchy, then looks for an individual ID record that exists at multiple levels.

;WITH ClassTree
           AS (SELECT ID, NAME, Parent_ID, 1 as 'Level'
               FROM   TableName
               WHERE  ID = 1
               UNION ALL
               SELECT T.ID, T.NAME, T.Parent_ID, Level + 1 as 'Level'
               FROM   TableName T WITH (NOLOCK)
                      JOIN ClassTree
                        ON Parent_ID = ClassTree.ID
)

SELECT *
FROM ClassTree c1
WHERE EXISTS (SELECT 1 FROM ClassTree c2
              WHERE c2.id = c1.id
              AND c2.Level > c1.level)
share|improve this answer
    
You need something in recursion to break the loop. –  Mikael Eriksson Apr 5 '12 at 14:20
    
@MikaelEriksson He can run it from SSMS and kill it pretty easily. Results should still show on the cancelled execution. –  JNK Apr 5 '12 at 14:36
    
Ah, did not think about that. Just tested and without maxrecursion it breaks on its own after 100 cycles. –  Mikael Eriksson Apr 5 '12 at 14:39
    
@MikaelEriksson Admittedly a hackish solution but it should still work :) –  JNK Apr 5 '12 at 14:41
    
Yes it does. It will find one cycles. The 100 rows returned is the first found cycle that repeats itself. BTW it is in situations like this you need a hackish solution :). –  Mikael Eriksson Apr 5 '12 at 14:44

Here is a little something that you can use to find your cycles.

declare @T table
(
  ID int,
  Parent_ID int
)

insert into @T values
(1, 3),(2, 1),(3, 2),    -- This is a cycle
(4, 4),                  -- This is a cycle
(5, null),(6, 5),(7, 6)  -- This is not a cycle

;with C as
(
  select T.ID,
         T.Parent_ID,
         cast(',' + cast(ID as varchar(10)) + ',' as varchar(max)) as Path,
         0 Cycle
  from @T as T
  union all
  select T.ID,
         T.Parent_ID,
         C.Path + cast(T.ID as varchar(10)) + ',',
         case when C.Path like '%,'+cast(T.ID as varchar(10))+',%' 
           then 1 
           else 0 
         end
  from @T as T
    inner join C  
      on T.Parent_ID = C.ID
  where C.Cycle = 0
)
select *
from C
where Cycle = 1

Result:

ID          Parent_ID   Path       Cycle
----------- ----------- ---------- -----------
4           4           ,4,4,      1
3           2           ,3,1,2,3,  1
2           1           ,2,3,1,2,  1
1           3           ,1,2,3,1,  1
share|improve this answer

The very first thing I would do is change this:

JOIN ClassTree
     ON Parent_ID = ClassTree.ID 

to this:

JOIN ClassTree                         
     ON t.Parent_ID = ClassTree.ID 

It might be trying to join ClassTree to itself since you didn't specify the table alias in the join.

share|improve this answer
    
Nice observation. –  JNK Apr 5 '12 at 13:24
    
Without the table prefix you get Ambiguous column name 'Parent_ID' –  Mikael Eriksson Apr 5 '12 at 14:19

Thanks everybody for great help... I have found the error where i am doing wrong.. this is a case of circular reference, there is one parent_id which is inserted wrong at the time of insertion and this is created a whole problem as it is pointing wrong reference to parent object, i found it by checking levels for my objects. Thanks everybody.

share|improve this answer
2  
If one of the answers here led you to this solution, mark it as the answer. Otherwise, explain here how you found the loop (assuming it's different from the already proposed answers), and mark this as an answer. –  Nick Chammas Apr 6 '12 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.