February 20, 2008 at 12:56 am
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
February 20, 2008 at 5:22 am
any hope on these ?
February 20, 2008 at 5:47 am
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
February 20, 2008 at 10:55 am
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