473,324 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Variable locking up query ??

maxx233
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
4 1636
ck9663
2,878 Expert 2GB
For one, your query will not use index. Read this

-- CK
Feb 13 '09 #2
maxx233
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
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
2,878 Expert 2GB
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

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

Similar topics

2
by: Alan Hewat | last post by:
I have a relatively modest mySQL database with a PHP interface (70,000 entries). On a standard Dell Dimension 4400 (1.9 GHz with 512 Mbytes) running Suse 8.1 a typical query takes 0.66 sec with no...
0
by: Dave C. | last post by:
Hi there, We have some code that empties a table. Originally, the code used "delete from", but I am told that this method was either too slow, or held a lock for too long (this was a long time...
12
by: Puvendran | last post by:
Hi, We have encountered deadlock on a table which is used to generate sequential numbers for different categories eg typical entries Category Value TRADE_NO ...
3
by: Ryan | last post by:
I have a problem with record locking / blocking within an application. The app is quite straight forward. Written in Delphi 5 using BDE to access a SQL 7 database (Win2K server). Every so often...
2
by: Randall Sell | last post by:
Hello all, Somewhere on these newsgroups I recall reading that SQL Server 6 and prior (when they were married with Sybase) used page locking and not row level locking. Hence you could be locking...
8
by: dan.c.roth | last post by:
Hi I am having a debate with one of the Postgres developers (Tom Lane) according to him ms-sql does not implement predicate locking(SERIALIZABLE). Where predicate locking is defined as (from...
2
by: Zri Man | last post by:
I have a situation that is as follows: First an Index of what is discussed below: FED_A ->> Federated Objected/Table that has lots of rows LOC_B ->> Local Table which is selected and...
0
by: shakahshakah | last post by:
Just started investigating InnoDB after having used MyISAM tables in the 4.0.12 version, almost immediately ran into a locking issue with INSERTs, DELETEs, and transactions. Given the following...
1
by: ABrown | last post by:
Hello, I have a 2003 database set up with about 20 users (only about 4 at a time) but I repeatedly get a problem with the records all locking. Each User is assigning billing codes to jobs so they...
9
by: zmickle | last post by:
Experts and books all say that you can share an Access back end on a shared drive with the front end running on each host computer. I have a simple database that tracks student data and it is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.