SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query slows down with extra item in Where clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/07/2012 :  04:18:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 11/07/2012 :  04:23:53  Show Profile  Reply with Quote
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

Edited by - bandi on 11/07/2012 04:25:12
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/07/2012 :  04:44:27  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2012 :  09:32:46  Show Profile  Reply with Quote
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/

Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/07/2012 :  09:55:45  Show Profile  Reply with Quote
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).
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  11:46:48  Show Profile  Reply with Quote
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;
Go to Top of Page

andersqwe
Starting Member

United Kingdom
4 Posts

Posted - 11/07/2012 :  20:26:23  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 11/08/2012 :  02:13:08  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000