Archive for July, 2014

Optimizing (replacing) recursive cte’s

July 24, 2014

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

create a sql server linked server with a name different to the server that is being linked to

July 1, 2014

Provider = sql server native client
datasource = server name
catalog = database

key is not to use server type = sql server in the gui