EXPLAIN EXTENDED

How to create fast database queries

SQL Server: deleting with self-referential FOREIGN KEY, handling loops

with 2 comments

From comments to one of my previous posts: SQL Server: deleting with self-referential FOREIGN KEY

In my case the query goes to infinite loop.

If I use OPTION (MAXRECURSION 365) or OPTION (MAXRECURSION 3267), it fails when reaching highest recursion value.

If I use 0 (which is infinite) the query runs for an hour with no output.

I understand it is a data related issue which is causing infinite loop, but I cannot control the data being sent to the DB.

Any other way we can handle it?

Thanks,
Jay

That's a common situation, and we would sometimes need to clean the mess up (i. e. remove the whole loop).

To do this, we would just need to add a little check condition to the recursive CTE.

We just record the topmost entry (or better say the entry we first use in the loop, as there is obviously no topmost entry), and check if we ever come across it again as we do the recursion.

Once we have it on next iteration, we know it's a loop and it's time to stop.

Here's how we do this:

CREATE TABLE TestComposite (
        id INT NOT NULL, 
        siteUrl NVARCHAR(255) NOT NULL,
        name NVARCHAR(MAX) NOT NULL,
        parentId INT NULL,
        PRIMARY KEY (id, siteUrl),
        FOREIGN KEY (parentId, siteUrl) REFERENCES TestComposite (id, siteUrl)
)
GO

INSERT
INTO    TestComposite
VALUES  (1,  'site1', 'name1', NULL),
        (2,  'site2', 'name2', 4),
        (3,  'site2', 'name3', 2),
        (4,  'site2', 'name4', 3),
        (5,  'site5', 'name5', NULL),
        (6,  'site5', 'name6', 5)
GO

WITH    q (start, id, siteUrl) AS
        (
        SELECT  id, id, siteUrl
        FROM    TestComposite
        WHERE   id = 2
                AND siteUrl = 'site2'
        UNION ALL
        SELECT  start, tc.id, tc.siteUrl
        FROM    q
        JOIN    TestComposite tc
        ON      tc.parentID = q.id
                AND tc.siteUrl = q.siteUrl
        WHERE   tc.id <> q.start
        )
DELETE
FROM    TestComposite
OUTPUT  DELETED.*
WHERE   EXISTS
        (
        SELECT  id, siteUrl
        INTERSECT
        SELECT  id, siteUrl
        FROM    q
        )

We had a loop on 2 -> 3 -> 4 which is gone after the query.

See this query on SQL Fiddle.

Written by Quassnoi

July 10th, 2014 at 9:00 pm

Posted in SQL Server

2 Responses to 'SQL Server: deleting with self-referential FOREIGN KEY, handling loops'

Subscribe to comments with RSS

  1. Hey ,

    I tried you way but it ended in loop again and gave the same error, max xx reached..
    I appreciate your help . If thers anythying else you could help me with.

    Thanks and Regards,
    J

    J

    11 Jul 14 at 20:23

  2. @J: sorry, can’t help you otherwise without seeing your data. We could schedule a consulting session if you are ready to give me access. Please contact me on the contact page http://explainextended.com/drop-me-a-line for prices and details, if you’re interested.

    Quassnoi

    11 Jul 14 at 20:34

Leave a Reply