Weird Issue with Between - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Weird Issue with Between

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-09-22 : 11:53:22

Hey all,
A member of my team showed me a problem that I couldn't solve today.
He's executing a query on a table that contains about 8M rows of Doctor's Appointments. One of the fields in his where clause is AppointmentDate DATETIME(8).

Here's where things get weird. He's got a query that searches for appointments between two datetimes.
So, he's got something like
AND a.AppointmentDate BETWEEN @BeginDate AND @EndDate

That generates a REALLY terrible execution plan that takes forever to execute. If he does this, it generates a great plan that executes nearly instantlly.
AND a.AppointmentDate BETWEEN '10/12/2005' AND '10/13/2005'

Any ideas?
We've REINDEX'd, and UPDATE STATISICS WITH FULL SCAN with no luck.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-22 : 11:58:47

weird... try putting the variables in a yyyymmdd format. are they datetime?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-22 : 12:33:46

This in a procedure right ?
[url]http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx[/url]

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-09-22 : 13:50:28

This might have something to do with it as well. I've tried both of these methods to no avail though.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271566

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-22 : 23:31:21

Insert the dates into two columns of a table variable and join to it.

See what that gives you. Let me know. I have some profiler data having the same issue. There are several million rows (don't ask). I'll try it on that data also.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-22 : 23:41:06

what's the datatype for @begindate and @enddate? there might be some really bad conversion going on, be sure that they have the same datatype as with AppointmentDate

HTH

quote:
Originally posted by MichaelP

Hey all,
A member of my team showed me a problem that I couldn't solve today.
He's executing a query on a table that contains about 8M rows of Doctor's Appointments. One of the fields in his where clause is AppointmentDate DATETIME(8).

Here's where things get weird. He's got a query that searches for appointments between two datetimes.
So, he's got something like
AND a.AppointmentDate BETWEEN @BeginDate AND @EndDate

That generates a REALLY terrible execution plan that takes forever to execute. If he does this, it generates a great plan that executes nearly instantlly.
AND a.AppointmentDate BETWEEN '10/12/2005' AND '10/13/2005'

Any ideas?
We've REINDEX'd, and UPDATE STATISICS WITH FULL SCAN with no luck.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 01:20:25

"AppointmentDate DATETIME(8)"

Michael, you're scaring me with that (8) after the datetime!

Is it a DATETIME, a VARCHAR(8), or DBASE II?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 01:46:48

>>AppointmentDate DATETIME(8).

Are you using SQL Server?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-23 : 04:26:18

yah,that's sql, he's just specifying the size

quote:

Date and Time Data
Date and time data consists of valid date or time combinations. For example, valid date and time data includes both "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01 AM 8/17/98". Date and time data is stored using the datetime and smalldatetime data types in Microsoft® SQL Server™ 2000. Use datetime to store dates in the range from January 1, 1753 through December 31, 9999 (requires 8 bytes of storage per value). Use smalldatetime to store dates in the range from January 1, 1900 through June 6, 2079 (requires 4 bytes of storage per value).

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 04:51:28

Well. But DateTime(8) made me to have little bit confusion

Declare @t DateTime(8)
set @t='20051010'
select @t

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 04:55:57

Yes me confused too

Server: Msg 2716, Level 16, State 1, Line 1
Column or parameter #-1: Cannot specify a column width on data type datetime.
Parameter '@t' has an invalid data type.

Duane.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-09-23 : 07:59:35

I get the same error on SQL Server 7 too

steve

Facts are meaningless. You could use facts to prove anything that's even remotely true!

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-09-23 : 11:52:57

Yeah, it's just a DATETIME. I'm running SQL 2000 EE SP3a.

BEGINDATE and ENDDATE are datetime, but they are being set like this:
SET BeginDate = '10/1/2005'

Could that be the issue?
Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-23 : 12:18:09

I have seen this type of behavior before. What happens is that when you have the values hard coded in the query, the query optimizer actually uses them during the optimization process, but when they are passed from local variables, the optimizer makes a plan based on less information. I saw an article once that explained this, but I don’t have a link.

Yes, sometimes dynamic SQL is faster! Especially for reporting queries where this type of situation is most likely to occur.

CODO ERGO SUM

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-09-23 : 12:36:51

Michael,

Are the variables you list in the query local variables or stored procedure parameters?

While I was at TechED this year, I attended a few DBA and DAT sessions focused on tuning and performance and learned about something that may help. It seems that when the query optimizer creates the plan, it makes a big difference if it is using parameters or local variables. If you are using parameters, it performs a sniff and then creates the plan based on the values that are passed in (see the link posted by rockmoose) the first time. However, the query optimizer is blind when it comes to local variables and therefore will be unable to use some or all of the statistics available.

I'm not quite sure this illustrates my point exactly, but execute the following code and view the execution plan while doing so. You'll notice that the two stored procedures have exactly the same query and generate the same execution plan, but the one that uses local variables has a higher cost.

[CODE]CREATE TABLE dbo.MyTestTable
(
Number INT NOT NULL Primary Key,
Range INT NOT NULL
);

INSERT MyTestTable
SELECT n1.n+n2.n+n3.n+n4.n+n5.n+1 Number, n1.n+n2.n+1 Range
FROM
(
SELECT 1 as n UNION ALL
SELECT 2 as n UNION ALL
SELECT 3 as n UNION ALL
SELECT 4 as n UNION ALL
SELECT 5 as n UNION ALL
SELECT 6 as n UNION ALL
SELECT 7 as n UNION ALL
SELECT 8 as n UNION ALL
SELECT 9 as n UNION ALL
SELECT 0 as n
) n1
CROSS JOIN
(
SELECT 0 as n UNION ALL
SELECT 10 as n UNION ALL
SELECT 20 as n UNION ALL
SELECT 30 as n UNION ALL
SELECT 40 as n UNION ALL
SELECT 50 as n UNION ALL
SELECT 60 as n UNION ALL
SELECT 70 as n UNION ALL
SELECT 80 as n UNION ALL
SELECT 90 as n
) n2
CROSS JOIN
(
SELECT 0 as n UNION ALL
SELECT 100 as n UNION ALL
SELECT 200 as n UNION ALL
SELECT 300 as n UNION ALL
SELECT 400 as n UNION ALL
SELECT 500 as n UNION ALL
SELECT 600 as n UNION ALL
SELECT 700 as n UNION ALL
SELECT 800 as n UNION ALL
SELECT 900 as n
) n3
CROSS JOIN
(
SELECT 0 as n UNION ALL
SELECT 1000 as n UNION ALL
SELECT 2000 as n UNION ALL
SELECT 3000 as n UNION ALL
SELECT 4000 as n UNION ALL
SELECT 5000 as n UNION ALL
SELECT 6000 as n UNION ALL
SELECT 7000 as n UNION ALL
SELECT 8000 as n UNION ALL
SELECT 9000 as n
) n4
CROSS JOIN
(
SELECT 0 as n UNION ALL
SELECT 10000 as n UNION ALL
SELECT 20000 as n UNION ALL
SELECT 30000 as n UNION ALL
SELECT 40000 as n UNION ALL
SELECT 50000 as n UNION ALL
SELECT 60000 as n UNION ALL
SELECT 70000 as n UNION ALL
SELECT 80000 as n UNION ALL
SELECT 90000 as n
) n5
Order by Number
GO

CREATE NONCLUSTERED INDEX ix_MyTestTable_Range1 ON MyTestTable(Range);
GO

CREATE STATISTICS st_MyTestTable_Range1 ON MyTestTable(Range)WITH FULLSCAN;
GO

CREATE PROCEDURE dbo.MyTestTableRangeSelectByParam
(
@RangeBeg INT = 50,
@RangeEnd INT = 54
)
AS
SELECT COUNT(*) CountOfRangedNumbers
FROM dbo.MyTestTable
AS n1
WHERE n1.Range
BETWEEN @RangeBeg
AND @RangeEnd;
GO

CREATE PROCEDURE dbo.MyTestTableRangeSelectByVar
AS

DECLARE @RangeBeg INT,
@RangeEnd INT;

SELECT @RangeBeg = 50,
@RangeEnd = 54;

SELECT COUNT(*) CountOfRangedNumbers
FROM dbo.MyTestTable
AS n1
WHERE n1.Range
BETWEEN @RangeBeg
AND @RangeEnd;
GO

EXEC dbo.MyTestTableRangeSelectByParam;

EXEC dbo.MyTestTableRangeSelectByVar;
GO

DROP PROCEDURE dbo.MyTestTableRangeSelectByVar;

DROP PROCEDURE dbo.MyTestTableRangeSelectByParam;

DROP TABLE dbo.MyTestTable;[/CODE]

Perhaps someone can provide a better example... I didn't have a whole lot of time.

rheitzman
Starting Member

16 Posts

Posted - 2005-09-23 : 17:14:58

quote:
Originally posted by MichaelP

...That generates a REALLY terrible execution plan that takes forever to execute. If he does this, it generates a great plan that executes nearly instantlly.
AND a.AppointmentDate BETWEEN '10/12/2005' AND '10/13/2005'

Might be best to use dynamic SQL/EXEC and take the compile hit on the execution.

RE: Datetime(8)

You can use smalldatetime in SQL Server which uses 2 bytes vs. 4 bytes for datetime. smalldatetime holds time to one minute accuracy.

RE: Join/equality on datetime fields

Really? I've always thought this was a bad idea. If I have had to index/join/test on dates I've used yyyymmdd strings or converted the data to dateparts.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-26 : 05:05:55

The query with

quote:
AND a.AppointmentDate BETWEEN '10/12/2005' AND '10/13/2005'

can use statistics on date column because concrete values are known.

Original query:

quote:
AND a.AppointmentDate BETWEEN @BeginDate AND @EndDate

can't use statistics on date because concrete values are not known before query is executed. Actually they can be calculated, but query optimiser doesn't try to do it.

The solution is to package query as a stored procedure with @BeginDate and @EndDate as parameters. In that case query optimizer will replace variables with their values before generating execution plan.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-26 : 06:26:45

have you tried any of the following

1. specify a time range like
declare @begindate datetime,@enddate datetime

set @begindate='10/12/2005 00:00:00'
set @enddate='10/13/2005 23:59:59'

...where a.appoointmentdate between @begindate and @enddate
2. use > and <
declare @begindate datetime,@enddate datetime

set @begindate='10/12/2005'
set @enddate='10/13/2005'

...where a.appointmentdate >@begindate and a.appointmentdate<@enddate

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-26 : 06:38:18

Jen, how about using this

set @begindate='20051012 00:00:00'
set @enddate='20051013 23:59:59'

than

set @begindate='10/12/2005 00:00:00'
set @enddate='10/13/2005 23:59:59'

to avoid conflict with local settings?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-26 : 14:13:58

I reckon we need a utility that changes the Locale of the server every day (or every 5 minutes!) so we can flush out stuff that is dependent on Locale.

We have a test server set to some weird collation setting, but we only get around to testing on it once in a blue moon, and its always a major pain to resolve all the issues. We need to get them resolved within an hour of writing the non-portable code before we've forgotten what its all about really.

Maybe I'll set the DEV server to CASE SENSITIVE overnight for a laugh!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-26 : 15:54:05

quote:
Maybe I'll set the DEV server to CASE SENSITIVE overnight for a laugh!

Pure evil...

You can mess with a lot of stuff,
but messing with the collations is striking under the belt for sure.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources