A very bizarre date/time problem!

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

SQL Server 2005 Forums

Transact-SQL (2005)

A very bizarre date/time problem!

Author

Topic

Architect
Starting Member

8 Posts

Posted - 2008-02-19 : 17:17:35

Running into a very bizarre problem here. I'm querying a couple of tables based on a date range. Nothing fancy; simply stuff. The query runs fine when I hard-code the dates, but when I use variables, the query just hangs.

This works fine:
[CODE]SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>='2008-02-09 00:00:00.000' AND A.SomeDate<'2008-02-09 01:00:00.000'
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
[/CODE]

This does NOT: NO clue why :confused:
[CODE]DECLARE @FromDate DATETIME
, @ToDate DATETIME

SELECT @FromDate = '2008-02-09 00:00:00.000'
SELECT @ToDate = '2008-02-09 01:00:00.000'

SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000[/CODE]

Any ideas?

-a

Architect
Starting Member

8 Posts

Posted - 2008-02-19 : 17:24:20

Update: The A.SomeDate column is indexed. The query with the variables does not make use of the index, where as the query with the hard-coded dates does make use of the index. That's why the query with the variables hangs. I'm able to work around this using a index hint, but am still curious why SQL Server is behaving this way.

Architect
Starting Member

8 Posts

Posted - 2008-02-19 : 17:40:11

Checked the execution plans on both queries and they are VERY different. Any ideas?

Architect
Starting Member

8 Posts

Posted - 2008-02-19 : 18:19:32

FINAL SOLUTION:

[CODE]DECLARE @FromDate DATETIME
, @ToDate DATETIME

SELECT @FromDate = '2008-02-09 00:00:00.000'
SELECT @ToDate = '2008-02-09 01:00:00.000'

SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
OPTION (OPTIMIZE FOR (@FromDate = '2008-02-09 00:00:00.000', @ToDate='2008-02-09 01:00:00.000'))[/CODE]

I hope this will help someone else experiencing a similar performance issue when using variables.

-a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 03:58:00

But if @FromDate is not equal to 200802009, then you are back at square one again.
This phenomenon is called parameter sniffing.

E 12°55'05.25"
N 56°04'39.16"

Architect
Starting Member

8 Posts

Posted - 2008-02-22 : 01:08:54

Actually, it performs fine regardless of the value of @FromDate. Using the OPTIMIZE FOR just ensures that the proper (faster) execution plan is used, which is what I needed. I've tested this with several different dates, and the performance is fine.

-a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources