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
