CODEWITHSUNDEEP
sqlt-sql
Sk93
Sk93

Reputation: 3718

Improving recursive SQL looping

I am trying to solve a performance issue on an inherited system that appears when we have a significant amount of data.

We have a table that contains the two fields "ItemID" and "ParentItemID". The "ParentItemID" field relates to another row in the same talbe where the "ItemID" field matches this row's "ParentItemID" field.

This relationship can be many, many rows deep in places.

The following query is being run and looks like it could be another cause of slowdown:

WHILE 1=1
BEGIN
    SELECT @ParentID = ParentItemID FROM Items WHERE ItemID = @LastParentID
    IF @parentID IS NULL
    BEGIN
        break
    END
    ELSE
    BEGIN
        SET @LastParentID = @ParentID
    END
END

Is there a better way of doing this sort of recursive search?

note: we are NOT allowed to make table changes at this point, so adding a "RootItemID" column is not possible (I've already asked, as this would solve the problem outright!)

Upvotes: 0

Views: 46

Answers (2)

Deep
Deep

Reputation: 3202

you can do it by a Common Table Expression like :

;WITH cte_hierarchy
     AS (SELECT *
         FROM   item
         WHERE  ItemID = @ParentID
         UNION ALL
         SELECT i.*
         FROM   item i
                JOIN cte_hierarchy h
                  ON i.ItemID = h.ParentItemID)
SELECT *
FROM   cte_hierarchy 
WHERE  .....

Upvotes: 1

Paddy
Paddy

Reputation: 33867

You could use a common table expression for this:

WITH Antecedents (ITemID, ParentItemID, Level)
AS
(
    -- Anchor member definition
    SELECT ItemID, ParentItemID, 0 AS Level FROM Items WHERE ItemID = @StartingID

    UNION ALL

    SELECT ItemID, ParentItemID, Antecedents.Level + 1 AS Level 
    FROM Items 
    INNER JOIN Antecedents
      ON Antecedents.ParentItemID = Items.ItemID

)

SELECT TOP 1 @LastParentID = ItemID 
FROM Antecedents
ORDER BY Level DESC

More info on recursive CTE's here:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Upvotes: 3

Related Questions