which is faster? - 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)

which is faster?

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-11 : 13:07:53

Using:
- WHERe datestart between '01/01/2002' and '01/31/2002'
- or using datepart to parse the days from 1-31, month as 01, and year as 2002

I don't know the syntax for the second part and I'm curious which would execute faster. I know the first syntax is definitely faster using "datestart >= '01/01/2002' and datesstart <= '01/31/2002'".

ankurgupta26
Starting Member

32 Posts

Posted - 2002-10-11 : 13:26:58

I feel the 1st option is best, simple and more readable than the 2nd one. Don't make your query look complex for no reasons.

Thanks !
AnkuR.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-11 : 14:17:58

Another option is:


SELECT myfield
FROM MyTable
WHERE datestart BETWEEN @DateStart AND DATEADD(mm, 1, @DateStart)

This will add exactly 1 month to your start date. This may be dangerous if your start date contains any time info. Bottom line, do some testing with this.

Also remember that if you are using date and time for the "datestart" field, you need '1/1/2002' to '2/1/2002' because that's from the first second of 1/1/2002 to the last second of 1/31/2002.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-11 : 15:18:43

So just write


SELECT myfield
FROM MyTable
WHERE datestart >= @DateStart
  AND datestart < DATEADD(mm, 1, @DateStart)

and stop worrying.

There's nothing magical about BETWEEN. The query plan for "x BETWEEN 1 AND 10" will still say "x >= 1 and x <= 10".

As far as taking apart the date, I concur: for continuous timeranges it's not a good option. You're not even giving the query optimizer the option of using an index. Even if it uses a scan for both methods, it will still take longer to do all the YEAR and MONTH calculations than a range check.

Edited by - Arnold Fribble on 10/11/2002 15:30:48

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-11 : 17:55:54

Well, this was one of the questions in the job screening test I wrote and I didn't know what to answer. I made a guess and I went for the syntax that uses between.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-12 : 02:21:48

BLAH, screening question? Don't get me wrong, I've passed every screening question with flying colors, but why do people bother with these? I hate them. (OK, actually I just hate the whole HR screening process in general, but oh well.)

It's pretty simple to memorize general questions like this. I mean, look at all the paper certified people out there. (yay me.) They'll know the answer, but won't know why it's true. I don't mean to slight you, I really don't, but if you don't know the answer and WHY it's the correct answer, you'll flunk the guy who says "It depends, I can't give you an answer without knowing more about the problem.", and pass the guy who's like "Duh, no brainer, BETWEEN would always be better."

I've actually seen a case where using tr_effdate <= @ToDate absolutely killed the server. (seriously, the data entry people were complaining about slowdown.) While DATEADD(mm, 0, tr_effdate) <= @ToDate caused the query to return in seconds. Would you care to guess why?

Oh well, sorry, just a rant.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-12 : 03:29:56

quote:

I've actually seen a case where using tr_effdate <= @ToDate absolutely killed the server. (seriously, the data entry people were complaining about slowdown.) While DATEADD(mm, 0, tr_effdate) <= @ToDate caused the query to return in seconds. Would you care to guess why?

Probably bad estimation of the rowcount: if it estimates low enough, it could generate a plan with an index seek and bookmark lookup. If it turns out that there's a large number of matching rows, the bookmark lookups will be much slower than a scan. Inserting the DATEADD prevents the possibility of an index seek.

lozitskiy
Starting Member

28 Posts

Posted - 2002-10-12 : 10:43:11

Be sure to have clustered index on field datestart

-------------
MCP MSSQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources