SQL Server to retain the query plan in cache, or something like that. This i
s
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?http://www.aspfaq.com/2280
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
> allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
> <end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"Aaron Bertrand [SQL Server MVP]" wrote:
> http://www.aspfaq.com/2280
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
>
>|||I would think you would want to use DateDiff(), but that's just me.
Camps for Troubled Teens
http://www.brat-camps.com
Brat Camps - Free Search; Results Fast
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This
is
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?|||I have provided a example of why using datediff is not a correct solution in
this example.
Using between (as you will see in query anyliser) it will do an index s
,using the datediff method causes it to ignore the index and scan the full
table.
IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
INSERT INTO
#tmpDates
SELECT
DATEADD( d , -n , GETDATE() ) AS myDate
FROM
tblNumbers
WHERE
N BETWEEN 1 AND 20
/* uses indexed s
*/SELECT
*
FROM
#tmpDates
WHERE
myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
/* forces scan */
SELECT
*
FROM
#tmpDates
WHERE
DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
AND
DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
"Brat Camps" <spam at diamondflex dot com> wrote in message
news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> I would think you would want to use DateDiff(), but that's just me.
> --
> Camps for Troubled Teens
> http://www.brat-camps.com
> Brat Camps - Free Search; Results Fast
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
<end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>
>|||Hammer that box! Woo Hoo!!!
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Rebecca York" wrote:
> I have provided a example of why using datediff is not a correct solution
in
> this example.
> Using between (as you will see in query anyliser) it will do an index s
,
> using the datediff method causes it to ignore the index and scan the full
> table.
>
> IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
> CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
> INSERT INTO
> #tmpDates
> SELECT
> DATEADD( d , -n , GETDATE() ) AS myDate
> FROM
> tblNumbers
> WHERE
> N BETWEEN 1 AND 20
> /* uses indexed s
*/> SELECT
> *
> FROM
> #tmpDates
> WHERE
> myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
> /* forces scan */
> SELECT
> *
> FROM
> #tmpDates
> WHERE
> DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
> AND
> DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
>
>
> "Brat Camps" <spam at diamondflex dot com> wrote in message
> news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> allows
> <end_date>
>
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"John Barr" wrote:
> Which is better to use? I heard that <= and >= are better because it allow
s
> SQL Server to retain the query plan in cache, or something like that. This
is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date
>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||On Fri, 7 Oct 2005 07:44:04 -0700, John Barr wrote:
>Is there any information on caching or query plan reuse? Basically, I am
>looking for information pertaining to the fact that SQL Server converts a
>Between to a >= and <= pair, and if you resubmit the same between statement
,
>it converts it again and regenerates it plan. If you use the >= and <=
>instead, it will reuse the existing plan to re-execute the statement. Is th
is
>true? and if so, is there documentation on it?
Hi John,
This is not true. It would be quite silly if MS had overlooked this - if
each proc that uses a BETWEEN is recompiled on each execution, DBA's
would be screaming and shouting at MS.
To test this, simple start Profiler, run the following code, then check
for any SP:Recompile events in the profiler trace.
use pubs
go
create proc dbo.test
as
select * from authors where au_fname between 'A' and 'C'
go
exec dbo.test
go
exec dbo.test
go
exec dbo.test
go
drop proc dbo.test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||John
If you the BETWEEN, and you have a usable index so that the plan can be
autoparameterized, you will see that the cached plan in syscacheobjects has
already converted it to use the >= and <=
So then running the query with the >= and <= will use the already cached
plan. No new plan will need to be created.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:0961B42D-005E-4AEB-8143-97472DF54BBB@.microsoft.com...
> Is there any information on caching or query plan reuse? Basically, I am
> looking for information pertaining to the fact that SQL Server converts a
> Between to a >= and <= pair, and if you resubmit the same between
> statement,
> it converts it again and regenerates it plan. If you use the >= and <=
> instead, it will reuse the existing plan to re-execute the statement. Is
> this
> true? and if so, is there documentation on it?
>
> "John Barr" wrote:
>
>|||Since they compile the same way, ask yourself which form gives you
easier to read and maintain code. You will pick BETWEEN for the poor
guy that comes after you.
