SQL Server Performance

Query optimization in query that uses CTE.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by suren_smh, Dec 15, 2005.

  1. suren_smh New Member

    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
  2. Luis Martin Moderator

    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.



  3. suren_smh New Member

    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
  4. mmarovic Active Member

    What is lgr and ugr column data type?
  5. mmarovic Active Member

    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.
  6. mmarovic Active Member

    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, '%') 
  7. suren_smh New Member

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

    Regards,
    -Surendra

Share This Page