Query optimization in query that uses CTE.

Hi Guys,
This is my first entry to this forum.
i am facing one problem.
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

FROMtab1 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
FROMSelPath 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
SELECTpath_id,x,y,z,r

FROMSelPath a
INNER JOIN pce.qq c ON a.r = c.r

ORDER BY x

-----------------------------------------------------

Reply soon...
bye
take care

Regards,
-Surendra

Hi Luis,
thnaks for thw reply.
I used the Tuning Advicer ,
but can you please suggest any modification in the query.
I want to show the hierarchy of some courses here.

I would appreciate the answer from you.

bye



Regards,
-Surendra

What is lgr and ugr column data type?

Forget about the previous question, I see it is an int. So I don't think you need to check if @LowerGradeRange = ''. Next part:

quote:
CASE
WHEN ISNULL(@LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@LowerGradeRange,'')<>'' AND b.lgr >= @LowerGradeRange THEN 1
END=1

I would rewrite as:
b.lgr > isNull(@LowerGradeRange, -1)
I guess ranges can't be negative. The same for the other range.

Instead of setting the @keyWord to '' when it is null and next condition:

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

I would use:

Code:

c.y like isNull(@keyword, '%') 

hi,
Thanks all for the valuable suggestions.
It helps me a lot.
Regards,
Surendra

Regards,
-Surendra