date range causing Long Execution Times

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

date range causing Long Execution Times

Author

Topic

noyellatmonkeys
Starting Member

15 Posts

Posted - 2010-01-10 : 13:49:30

Hello all,

So i am not sure why this is happening but when I execute this code everything is fine and it returns quickly

select distinct x.ref, count(x.id)as leads, 
sum(case x.doubleoptin when '1' then 1 when '0' then 0 end) as DoubleOptin, 
sum(case x.bademail when '1' then 1 when '0' then 0 end) as bademails, 
count(C.adid) as ClickActivity 
from signups x 
left outer join advertsclickedbyuser c on c.userid = x.sessionid 
where x.signupsite = '1111' and x.firstname is not null 
group by x.ref 
order by leads desc

but when I try to add a date range to it, it slows way down... it got up to 8 min of executing time before i just cancelled it

select distinct x.ref, count(x.id)as leads, 
sum(case x.doubleoptin when '1' then 1 when '0' then 0 end) as DoubleOptin, 
sum(case x.bademail when '1' then 1 when '0' then 0 end) as bademails, 
count(C.adid) as ClickActivity 
from signups x 
left outer join advertsclickedbyuser c on c.userid = x.sessionid 
where x.signupsite = '1111' and x.signupdate > '2010/1/1' and x.signupdate < '2010/1/10' and x.firstname is not null 
group by x.ref 
order by leads desc

I tried a few things like changing From signups to a select statement so that it was like from (select * from signups where "daterange") but the execution time was still extremely long.

Does anyone have any ideas?

Thanks and much appreciated

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-10 : 14:14:19

Do you have index on SignedupDate Column?