Query slows down with extra item in Where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query slows down with extra item in Where clause

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-07 : 04:18:00

Hi all

I've got what should be a simple query to pull back a group of records from a table.
The code is here:-


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)

If I take out "and ref.Service='0845'" from the WHERE clause it runs in about 2 minutes. Adding that line back in increases the time taken to run the same query to anything up to an hour.

I've created an index (non-clustered) on the reference table Service field (it already had a clustered index on another field) but that doesn't seem to have helped at all.

Anyone any ideas as to why adding the extra bit into the WHERE clause adds so much time?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-07 : 04:23:53

hi,
I am not sure about this.. But one trail is to filter records in FROM clause itself....


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
                and ref.Service='0845'

where
	ccd.Received >= @Start and ccd.Received < @End

--
Chandu

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-07 : 04:44:27

I've tried that and it gives me the same results.
I just can't figure out why adding something to reduce the number of records retrieved means the query takes longer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-07 : 09:32:46

quote:
Originally posted by rmg1

I've tried that and it gives me the same results.
I just can't figure out why adding something to reduce the number of records retrieved means the query takes longer.

It simply means the execution plan was not too great on addition on new condition. check and see what are costly steps and analyse if you can add index to speed it up etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-07 : 09:55:45

I've checked for adding indexes, but there's only one to add and that's on a completely different table to the ones I'm trying to use (this is part of a bigger piece of script).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-07 : 11:46:48

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;

andersqwe
Starting Member

4 Posts

Posted - 2012-11-07 : 20:26:23

Apologies if I have missed the point not slept for over 20 hours and am trying to stay awake :( against my will :)

sounds to me like Service field on the ref_CASOriginsToServices object needs adding to an indexs key, generally speaking any queries that are frequently ran should have all fields in the where and join clauses made part of an indexs key. I am not sure if this is a unique value or if duplicates are allowed but looking at the queries instinct tells me that possibly creating a new clustered index may improve performance.

IF this is already the case try rebuilding the index and set pad index to 70

Its me

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-11-08 : 02:13:08

Sunitabeck - just trying the recompile option now so I'll let you know how that goes.

Andersqwe - I can add a clustered index on the field as the table already has a clustered index on another field. The Service field is indexed but it's not a clustered index. Duplicates are allowed on this field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources