Optimizing (replacing) recursive cte’s

Recursive cte’s do not perform well when you are dealing with millions of records.

The following approach is not particularly elegant but performs so much better.

–Create table to store results

CREATE TABLE #RESULTS(ID INT NOT NULL, PARENTID UNIQUEIDENTIFIER NOT NULL, CHILDID UNIQUEIDENTIFIER, LEVEL INT IDENTITY(1,1) NOT NULL);

SET IDENTITY_INSERT #RESULTS ON;

INSERT #RESULTS(ID,PARENTID,CHILDID,LEVEL)
SELECT ID,PARENTID,CHILDID,1
FROM #SOURCE
WHERE PARENTID=@START;

WHILE @@ROWCOUNT>0
INSERT #RESULTS(ID,PARENTID,CHILDID,LEVEL)
SELECT S.ID,S.PARENTID,S.CHILDID,SCOPE_IDENTITY()+1
FROM #RESULTS AS R
JOIN #SOURCE AS S
ON S.PARENTID = R.CHILDID
WHERE LEVEL = SCOPE_IDENTITY();

The scope indentity function tells us the value of the last identity that was inserted within the current scope.
The while will discontinue when no more rows are inserted.
You could achieve similar results using an int variable rather than an identity field if you prefer

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: