Case Statement using Where Clause

  • It takes the Index scan eventhough 'sCustomerSys' column is Clustered Index.

    Please suggest if any alternative query on these ?

    DECLARE @StatusOp as VARCHAR(20)

    DECLARE @status as VARCHAR(20)

    SET @StatusOp = ' '

    SET @status = 'UNET'

    SELECT (dateadd(dd,datediff(dd,0,dtOrigRecv),0)) InquiryDate, SUBSTRING(sRefDocId,4,5) as JulianDate,

    COUNT(sProcessProject) Volume FROM mxDocument

    where (DATEADD(dd,DATEDIFF(dd,0,dtOrigRecv),0) BETWEEN '01/15/2008' AND '01/20/2008')

    AND sProcessProject LIKE 'GRP%'

    AND Case @StatusOp

    when ' ' then 1

    When 'eq' then

    case when sCustomerSys like (@Status) then 1 else 0 end

    end = 1

    GROUP BY (dateadd(dd,datediff(dd,0,dtOrigRecv),0)),SUBSTRING(sRefDocId,4,5)

    ORDER BY (dateadd(dd,datediff(dd,0,dtOrigRecv),0))

    thanks & regards

    Saravanakumar.R

  • any hope on these ?

  • Can you post the full execution plan? You might be hitting the scan because of other things within the query, such as performing functions on the columns then using them as part of the WHERE clause.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Saravanakumar.Rathinam (2/20/2008)


    It takes the Index scan eventhough 'sCustomerSys' column is Clustered Index.

    Please suggest if any alternative query on these ?

    The case statement prevents the search on sCustomerSys from being optimized. The only 'hard' criteria provided is on sProcessProject.

    It appears you want to optionally search on sCustomerSys for @status based on the value of @StatusOp. If so, set @status to '%' if @StatusOp is 'eq'.

    -- existing

    DECLARE @StatusOp as VARCHAR(20)

    DECLARE @status as VARCHAR(20)

    SET @StatusOp = ' '

    SET @status = 'UNET'

    SELECT ...

    FROM mxDocument

    where (DATEADD(dd,DATEDIFF(dd,0,dtOrigRecv),0)

    BETWEEN '01/15/2008' AND '01/20/2008')

    AND sProcessProject LIKE 'GRP%'

    AND Case @StatusOp

    when ' ' then 1

    When 'eq' then

    case when sCustomerSys like (@Status) then 1 else 0 end

    end = 1

    ....

    -- suggestion

    DECLARE @StatusOp as VARCHAR(20)

    DECLARE @status as VARCHAR(20)

    SET @StatusOp = ' '

    SET @status = 'UNET'

    IF @StatusOp = 'eq' SET @status = '%'

    -- now @status will always have the search value

    SELECT ...

    FROM mxDocument

    where

    dtOrigRecv >= '01/15/2008'

    and dtOrigRecv < dateadd(day,1,'01/20/2008')

    -- adjust end date of range so any index on dtOrigRecv can be used

    AND sProcessProject LIKE 'GRP%'

    AND sCustomerSys like @status

    ...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply