Just for laughs, add a statement level recompilation hint to see if that makes any difference. What I suspect is that you are stuck with a bad query plan and this should force the regeneration of the query plan.select
DATEPART(month,ccd.Received) as [Month]
,DATEPART(YEAR,ccd.Received) as [Year]
,COUNT(ccd.callid) as Calls
from
[NOC Reporting].dbo.CAS_Call_detail (nolock) ccd
inner join Reference.dbo.ref_CASOriginsToServices (nolock) ref
on ccd.Origin=ref.Origin
and ccd.Calltype=ref.CallType
where
ccd.Received >= @Start and ccd.Received < @End
and ref.Service='0845'
group by
DATEPART(month,ccd.Received)
,DATEPART(YEAR,ccd.Received)
OPTION (RECOMPILE);
If that does not work, Give this a try
CREATE TABLE #tmp(ServiceId VARCHAR(255));
INSERT INTO #tmp VALUES ('0845');
select
DATEPART(month,ccd.Received) as [Month]
,DATEPART(YEAR,ccd.Received) as [Year]
,COUNT(ccd.callid) as Calls
from
[NOC Reporting].dbo.CAS_Call_detail (nolock) ccd
inner join Reference.dbo.ref_CASOriginsToServices (nolock) ref
on ccd.Origin=ref.Origin
and ccd.Calltype=ref.CallType
INNER JOIN #tmp t ON t.ServiceId = ref.Service
where
ccd.Received >= @Start and ccd.Received < @End
--and ref.Service='0845'
group by
DATEPART(month,ccd.Received)
,DATEPART(YEAR,ccd.Received);
drop table #tmp;
Query slows down with extra item in Where clause
Posted - 11/07/2012 : 04:18:00
