Search This Blog

Wednesday, November 16, 2011

Alternative of CTE

Few days back I was facing a performance issue due to CTE. Then I replaced the CTE with set of sql statements and got the performance boost of 96%.
Here is the sample code which I used :

CREATE TABLE #t (assetid INT, assetname varchar(25), parentassetid INT)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(1,'aa',null)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(100,'a',1)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(101,'b',100)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(102,'c',100)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(103,'d',101)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(105,'e',102)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(106,'f',108)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(107,'e',102)


CREATE TABLE #item (id INT identity(1,1), assetid INT, assetname varchar(25), lvl INT)
DECLARE @counter INT
SET @Counter = 0
INSERT INTO #item (assetid,assetname,lvl) SELECT assetid,assetname, @counter FROM #t WHERE parentassetid = 1

WHILE @@ROWCOUNT > 0
BEGIN

SET @counter = @counter + 1

INSERT INTO #item (assetid,assetname,lvl) SELECT a.assetid,a.assetname,@counter FROM #t a INNER JOIN #item b
on a.parentassetid = b.assetid and exists (SELECT 1 FROM #item c WHERE b.assetid = c.assetid and c.lvl = (@counter-1))

END
SELECT * FROM #item

I hope this will help you!!!!