SQL 2005 Forcing execution path

[KIAman]

I have a query that looks something like this

-- sum up totals for each day starting with the first day of 
-- current month midnight up to current day midnight 

SELECT datetimestamp, SUM(total1) + SUM(total2) as totalsomething
FROM cooltable
WHERE total_type = 1
  AND datetimestamp BETWEEN DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
  AND convert(varchar(12), getdate())
GROUP BY datetimestamp

This takes 4 minutes to run and the execution path shows a complete table scan which took 3 minutes and 55 seconds then does a hash match partial aggregate which took 5 seconds.

Compare to this code

-- sum up totals for each day starting with the first day of 
-- current month midnight up to current day midnight 

SELECT datetimestamp, SUM(total1) + SUM(total2) as totalsomething
FROM cooltable
WHERE total_type = 1
  AND datetimestamp BETWEEN '2010-06-01'
  AND convert(varchar(12), getdate())
GROUP BY datetimestamp

This takes less than one second to run and the execution path shows index seek on nonclustered index at a cost of 0, while doing RID Lookup on the table "cooltable" which took a millisecond.

Both queries are identical except for calculating first day of month. How can I force SQL to change the execution path so I can run the first query under 1 second?

 

[Nothinman]

Have you tried putting the date into a variable before the SELECT statement and then referencing the variable in your BETWEEN statement?

 

[KIAman]

Yeah, I tried that but SQL takes the same path regardless. The query analyzer is too "smart" to be fooled by that, lol.

There's gotta be a way to trick SQL into a different execution path. I hope.

 

[WannaFly]

I have never used them but I think this is what they are for ... take a look at query hints...
http://msdn.microsoft.com/en-us/library/ms181714.aspx

 

[KIAman]

I've used query hints for insert, updates and merges but I wasn't sure if it would work for such a simple query.

Thanks, I got tons of reading material now.

 

[KIAman]

Well, nothing seemed to work so I blackmailed our DBA in making some custom indexes on the datetimestamp field and now it flies.

If I never post here again, I've been whacked by our DBA.

 

[Markbnj]

Well, nothing seemed to work so I blackmailed our DBA in making some custom indexes on the datetimestamp field and now it flies.

If I never post here again, I've been whacked by our DBA.

I would take steps immediately. The best defense is a strong offense!