locked
Query optimization in query that uses CTE. RRS feed

  • Question

  •  

    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
     

    Thursday, December 15, 2005 1:57 PM

Answers

  • Start by looking at the showplan output. You could create indexes on the columns used in your predicate although the predicates in your WHERE clause doesn't help the case.
    Friday, December 16, 2005 1:52 AM