Variable locking up query ??

[maxx233]

If I try to run the query below, it never spits anything out. If I change the bolded part to include hardcoded datetimes instead of variables containing the same values as what was hardcoded, then it runs fine. I'm sure it's just a stupid mistake or incompetance about something on my part.. can anyone point out what? ;)

CREATE TABLE #TTemp (Date datetime, CashIn money)
DECLARE @datetime datetime
SET @datetime = '12/01/2008 02:00:00'
INSERT INTO #TTemp (Date, CashIn) SELECT StartTime, CashIn FROM OrderDetail (nolock) WHERE IDType = 'P'
	AND [B]StartTime BETWEEN @datetime AND DATEADD(HOUR, 1, @datetime)[/B]
SELECT * FROM #TTemp
DROP TABLE #TTemp

Changing the bolded part to

StartTime BETWEEN '12/01/2008 02:00:00' AND '12/01/2008 03:00:00'

works fine and is very quick.

Here's some sample data I pulled up with this query:

SELECT TOP 5 StartTime, CashIn FROM OrderDetail WHERE IDType = 'P'
	AND StartTime BETWEEN '12/01/2008 02:00:00' AND '12/01/2008 03:00:00'

StartTime...... ............... .....CashIn
2008-12-01 02:00:14.000 9.55
2008-12-01 02:12:58.000 80.41
2008-12-01 02:13:17.000 96.15
2008-12-01 02:13:20.000 76.25
2008-12-01 02:13:26.000 1279.60


Thanks all!!!

[ck9663]

For one, your query will not use index. Read this

-- CK

[maxx233]

I'm still having the same issue, although I did fix the indexing issue (thanks for that, I didn't think of functions killing the indexing!) My current code is:

CREATE TABLE #TTemp (Date datetime, CashIn money)
DECLARE @datetime datetime
DECLARE @nextTime datetime
SET @datetime = '12/01/2008 02:00:00'
SET @nextTime = DATEADD(HOUR, 1, @datetime)
INSERT INTO #TTemp (Date, CashIn) SELECT StartTime, CashIn FROM OrderDetail (nolock) WHERE IDType = 'P'
AND StartTime >= @datetime AND StartTime < @nextTime
SELECT * FROM #TTemp
DROP TABLE #TTemp


I'm still at a loss on this one, are there any ideas out there? I've never had any problem using variables before, I just don't understand what's going wrong here.. I'm certain it's got to be crazy stupid though just cause it's bugging me so much ;)

maxx

[maxx233]

OK, Solved this. For anyone experiencing the same problem, it's an indexing issue. When using a datetime variable as a condition it no longer uses the indexing on the table. I don't understand why it makes any difference still. Perhaps explicitly converting my variables to a datetime when I'm SETting them would help. But what I did instead was force the query to use the StartTime index that's been set up on that table. Works great! I'm going to go back through a bunch of old queries and fix them now too, I'm sure I'll see a major improvement on what I thought was just a lot of calculation - turns out it's probably just a lot of table scanning! Oops!! Kudos to ck9663 for tipping me off to this, thanks!!

Here's my complete, working code:
CREATE TABLE #TTemp (Date datetime, CashIn money)
DECLARE @datetime datetime
SET @datetime = '12/01/2008 02:00:00'
INSERT INTO #TTemp (Date, CashIn) SELECT StartTime, CashIn FROM OrderDetail WITH (nolock, INDEX(IN_StartT ime)) WHERE IDType = 'P'
AND StartTime >= @datetime AND StartTime < DATEADD(HOUR, 1, @datetime)
SELECT * FROM #TTemp
DROP TABLE #TTemp

[ck9663]

What you did is to force the optimizer to use the index IN_StartTime. Do you have an index for IDTYPE and STARTTIME?

-- CK