Query optimization in query that uses CTE. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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.
—————————————————–
[email protected] INT
[email protected] INT
[email protected] INT
[email protected] 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
Use Tuning Advicer (SQL 2005 Tools) to find out if any indexes are neccesary.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
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
]]>