Query optimization in query that uses CTE.

Hi Guys,

I want to optimize one query which uses the CTE (server 2005 feature).
I am sending you the abstract query.

currently this query take 4-5 seconds to execute.
but i want to reduce it to 1 sec.
Plz, do help me, if someone know how to do it.

--
DECLARE @.X INT
DECLARE @.LowerGradeRange INT
DECLARE @.UpperGradeRange INT
DECLARE @.Keyword NVARCHAR(500)

SET @.X = 11500001
SET @.LowerGradeRange = NULL
SET @.UpperGradeRange = NULL
SET @.Keyword = ''

IF ISNULL(@.Keyword,'')=''
SET @.Keyword='';

WITH SelPath (path_id,x,y,z,r)
AS
(

-- Anchor member definition (returns base result set)
SELECT path_id,x,y,z,r

FROM tab1 a
INNER JOIN tab2 b ON a.x= b.x

WHERE
a.x = @.X
-- AND (a.parent IS NULL OR a.parent = 0)
AND
CASE
WHEN ISNULL(@.LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@.LowerGradeRange,'')<>'' AND b.lgr >= @.LowerGradeRange THEN 1
END=1
AND
CASE
WHEN ISNULL(@.UpperGradeRange,'')='' THEN 1
WHEN ISNULL(@.UpperGradeRange,'')<>'' AND b.ugr <= @.UpperGradeRange THEN 1

END=1
AND
CASE
WHEN @.Keyword <>'' AND b.y LIKE @.Keyword THEN 1
ELSE 1
END =1

UNION ALL

-- Recursive member definition
-- (returns the direct subordinate(s) of the activity in the anchor member result set)

SELECT path_id,x,y,z,r
FROM SelPath b
INNER JOIN tab1 a ON a.parent = b.path_id
INNER JOIN tab2 c ON a.x = c.x
WHERE
CASE
WHEN ISNULL(@.LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@.LowerGradeRange,'')<>'' AND c.lgr >= @.LowerGradeRange THEN 1
END=1
AND
CASE
WHEN ISNULL(@.UpperGradeRange,'')='' THEN 1
WHEN ISNULL(@.UpperGradeRange,'')<>'' AND c.ugr <= @.UpperGradeRange THEN 1

END=1
AND
CASE
WHEN @.Keyword <>'' AND c.y LIKE @.Keyword THEN 1
ELSE 1
END =1

)

-- Statement that executes the CTE
SELECT path_id,x,y,z,r

FROM SelPath a
INNER JOIN pce.qq c ON a.r = c.r

ORDER BY x

--

Reply soon...
bye
take care

Regards,
-Surendra

Go to the T-SQL forum. You'll get more help there.

-Jamie