By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Variable locking up query ??

maxx233
P: 32
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? ;)

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #TTemp (Date datetime, CashIn money)
  2. DECLARE @datetime datetime
  3. SET @datetime = '12/01/2008 02:00:00'
  4. INSERT INTO #TTemp (Date, CashIn) SELECT StartTime, CashIn FROM OrderDetail (nolock) WHERE IDType = 'P'
  5.     AND StartTime BETWEEN @datetime AND DATEADD(HOUR, 1, @datetime)
  6. SELECT * FROM #TTemp
  7. DROP TABLE #TTemp
  8.  
Changing the bolded part to
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 5 StartTime, CashIn FROM OrderDetail WHERE IDType = 'P'
  2.     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!!!
Feb 12 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
For one, your query will not use index. Read this

-- CK
Feb 13 '09 #2

maxx233
P: 32
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
Feb 16 '09 #3

maxx233
P: 32
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_StartTime)) WHERE IDType = 'P'
AND StartTime >= @datetime AND StartTime < DATEADD(HOUR, 1, @datetime)
SELECT * FROM #TTemp
DROP TABLE #TTemp
Feb 17 '09 #4

ck9663
Expert 2.5K+
P: 2,878
What you did is to force the optimizer to use the index IN_StartTime. Do you have an index for IDTYPE and STARTTIME?

-- CK
Feb 17 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.