Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Case Statement using Where Clause Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 12:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493
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
Post #457852
Posted Wednesday, February 20, 2008 5:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493
any hope on these ?
Post #457919
Posted Wednesday, February 20, 2008 5:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 15,645, Visits: 28,025
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #457924
Posted Wednesday, February 20, 2008 10:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:06 PM
Points: 438, Visits: 907
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
...

Post #458130
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse