|
|
|
SSC 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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49,
Visits: 493
|
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 13,371,
Visits: 25,152
|
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:31 PM
Points: 437,
Visits: 883
|
|
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 ...
|
|
|
|