Hello,

I need to run several queries that use 3 date args.  Is there any
significant different between the following select statements?  Performance? 
what you capture?

Select ....
Where (date1 between @a and @b) and (date2 between @c and @d)
and (date3 between @e and @f)

Or

Select ....
Where (date1>= @a and date1<=@b) and (date2>=@c and date2<=@d)
and (date3>=@e and date2<=@f)

The source data table contains between 35000 - 40000 rows and 35 columns.

Thanks,
Rich

A BETWEEN is essentially the same thing as a >= and <= and AFAIK the
optimizer will treat them the same way.

Show quoteHide quote

I believe you still see this in the execution plan, i.e., the BETWEEN is converted to >= and <=.

Show quoteHide quote

Show quote Hide quote No difference.

BETWEEN is not always convenient to use with datetimes. Especially not if
you need to compare whole days but the datetimes may have times other than
midnight. Usually >= and < makes more sense.

For example, this:

dt >= '20060101' AND dt < '20070101'

should be easier to type and understand than the equivalent expression using
BETWEEN:

dt BETWEEN '20060101' AND '2006-12-31T23:59:59.997'

Interestingly enough, I find that there is actually a speed/efficiency difference. As of yet, I don't truly understand the details -it seems as though they should both use indexes the same -but obviously not.

Based on the following test, I accept that BETWEEN will operate faster that the equivalent [ >= AND < ]. While the difference is small, it is measurable.

This test uses a very small table containing 100k rows.

Show quoteHide quote

Arnie,

I don't think your test measures anything to do with BETWEEN vs. </>. 
Virtually all of the time spent by your queries is to present the output
to the user, so you are measuring the difference between printing a
bunch of output when the output window is empty vs. printing a bunch of
output when the output window is full.

Try timing your two queries in the opposite order.  You may find, like I
did, that the second query is reproducibly the slower one.

Steve Kass
Drew University
www.stevekass.com

Arnie Rowland wrote:

Show quoteHide quote

Arnie Rowland wrote:
Show quoteHide quoteArnie,

1. I would suggest a much bigger dataset.

2.   Yur statement

SELECT *
    FROM #Test

will spend some time
2.1 accessing the data and
2.2 some time constructing and delivering the resultset

I would eliminate 2.2 altogether - if you have any differences in 2.1,
they will be more prononunced. Try this:

SELECT @c = count(*)
    FROM #Test
    WHERE TestDate >= '04/01/2000' AND TestDate < '04/01/2001'
-------------- possibly any non-indexable and simple criteria here
-- such as modulo of some numeric column = 1 an d modulo of some other
numeric column = 2

Good luck!

Good Ideas. I'll test completely removing the production of a resultset and
radically increase the dataset.

Show quoteHide quote

BETWEEN is not the same as >= and <.  It is equal to >= AND <=. They are different and can return different results depending on what your data looks like. The optimizer may even choose a different plan again depending on the data.

  Interestingly enough, I find that there is actually a speed/efficiency difference. As of yet, I don't truly understand the details -it seems as though they should both use indexes the same -but obviously not.

  Based on the following test, I accept that BETWEEN will operate faster that the equivalent [ >= AND < ]. While the difference is small, it is measurable.

  This test uses a very small table containing 100k rows.

  --
  Arnie Rowland, Ph.D.
  Westwood Consulting, Inc

  Most good judgment comes from experience.
  Most experience comes from bad judgment.
  - Anonymous


  /*
     Demonstration of Query speed effect from
     Using BETWEEN vs. [ >= AND < ]

     Kudos to the many who inspire.

  */

  CREATE TABLE #Test
    (    ID       int primary key
       , TestDate datetime
     )

  CREATE INDEX dt_index ON #Test ( TestDate )

  DECLARE
       @iCounter  int
     , @Start1    datetime
     , @Stop1     datetime
     , @Start2    datetime
     , @Stop2     datetime
     , @Start3    datetime
     , @Stop3     datetime

  SET @iCounter = 0

  SET NOCOUNT ON

  SET @Start1 = getdate()

  WHILE @iCounter < 100000

     BEGIN

        INSERT INTO #Test ( ID, TestDate )
           VALUES ( @iCounter, convert( datetime, '01/01/2000') + ( @iCounter / 4 ))

        SET @iCounter = ( @iCounter + 1 )

     END

  SET @Stop1 = getdate()

  SET @Start2 = getdate()

     SELECT *
     FROM #Test
     WHERE TestDate BETWEEN '04/01/2000' AND '03/31/2001'

  SET @Stop2 = getdate()

  SET @Start3 = getdate()

     SELECT *
     FROM #Test
     WHERE TestDate >= '04/01/2000' AND TestDate < '04/01/2001'

  SET @Stop3 = getdate()

  DROP TABLE #Test

  PRINT ( 'Loading Data...' )
  PRINT ( 'Start at: ' + cast( @Start1 AS varchar(30) ))
  PRINT ( ' Stop at: ' + cast( @Stop1 AS varchar(30) ))
  PRINT ( 'Time to Load Data: ' + cast( datediff( ms, @Start1, @Stop1 ) AS varchar(10) ))
  PRINT ( '' )
  PRINT ( 'BETWEEN Test' )
  PRINT ( 'Start at: ' + cast( @Start2 AS varchar(30) ))
  PRINT ( ' Stop at: ' + cast( @Stop2 AS varchar(30) ))
  PRINT ( 'Time Difference: ' + cast( datediff( ms, @Start2, @Stop2 ) AS varchar(10) ))
  PRINT ( '' )
  PRINT ( '>< Test ' )
  PRINT ( 'Start at: ' + cast( @Start3 AS varchar(30) ))
  PRINT ( ' Stop at: ' + cast( @Stop3 AS varchar(30) ))
  PRINT ( 'Time Difference: ' + cast( datediff( ms, @Start3, @Stop3 ) AS varchar(10) ))


Show quoteHide quote

Thanks for the comments -they help.

I was concerned about the issues that Steve related to output to display buffer. I had also tested in reverse order with no discernable change in the timing differences. I've even tested by using INSERT INTO instead of just a SELECT. While there is a substantial hit for the display output buffer, INSERT INTO a table variable or #Temp table still shows a measurable difference between the two.

I've now added proc buffer and cache purging. I'm trying to remove as much testing bias as possible.

AS Erland indicated, the difference shouldn't rate significance. And, if the two methods were interchangeable, the time difference would be just 'noise' -yet as the row count goes up in the table, it doesn't seem to drop to a level of insignificance -and I can't dismiss it as just 'noise'.

So far, all of the various permutations I've tried still show a very small, but measurable advantage to [BETWEEN].

I will soon post some additional results as I have them, and, who know, perhaps even a retraction from my original assertion.

  Interestingly enough, I find that there is actually a speed/efficiency difference. As of yet, I don't truly understand the details -it seems as though they should both use indexes the same -but obviously not.

  Based on the following test, I accept that BETWEEN will operate faster that the equivalent [ >= AND < ]. While the difference is small, it is measurable.

  This test uses a very small table containing 100k rows.

  --
  Arnie Rowland, Ph.D.
  Westwood Consulting, Inc

  Most good judgment comes from experience.
  Most experience comes from bad judgment.
  - Anonymous


  /*
     Demonstration of Query speed effect from
     Using BETWEEN vs. [ >= AND < ]

     Kudos to the many who inspire.

  */

  CREATE TABLE #Test
    (    ID       int primary key
       , TestDate datetime
     )

  CREATE INDEX dt_index ON #Test ( TestDate )

  DECLARE
       @iCounter  int
     , @Start1    datetime
     , @Stop1     datetime
     , @Start2    datetime
     , @Stop2     datetime
     , @Start3    datetime
     , @Stop3     datetime

  SET @iCounter = 0

  SET NOCOUNT ON

  SET @Start1 = getdate()

  WHILE @iCounter < 100000

     BEGIN

        INSERT INTO #Test ( ID, TestDate )
           VALUES ( @iCounter, convert( datetime, '01/01/2000') + ( @iCounter / 4 ))

        SET @iCounter = ( @iCounter + 1 )

     END

  SET @Stop1 = getdate()

  SET @Start2 = getdate()

     SELECT *
     FROM #Test
     WHERE TestDate BETWEEN '04/01/2000' AND '03/31/2001'

  SET @Stop2 = getdate()

  SET @Start3 = getdate()

     SELECT *
     FROM #Test
     WHERE TestDate >= '04/01/2000' AND TestDate < '04/01/2001'

  SET @Stop3 = getdate()

  DROP TABLE #Test

  PRINT ( 'Loading Data...' )
  PRINT ( 'Start at: ' + cast( @Start1 AS varchar(30) ))
  PRINT ( ' Stop at: ' + cast( @Stop1 AS varchar(30) ))
  PRINT ( 'Time to Load Data: ' + cast( datediff( ms, @Start1, @Stop1 ) AS varchar(10) ))
  PRINT ( '' )
  PRINT ( 'BETWEEN Test' )
  PRINT ( 'Start at: ' + cast( @Start2 AS varchar(30) ))
  PRINT ( ' Stop at: ' + cast( @Stop2 AS varchar(30) ))
  PRINT ( 'Time Difference: ' + cast( datediff( ms, @Start2, @Stop2 ) AS varchar(10) ))
  PRINT ( '' )
  PRINT ( '>< Test ' )
  PRINT ( 'Start at: ' + cast( @Start3 AS varchar(30) ))
  PRINT ( ' Stop at: ' + cast( @Stop3 AS varchar(30) ))
  PRINT ( 'Time Difference: ' + cast( datediff( ms, @Start3, @Stop3 ) AS varchar(10) ))


Show quoteHide quote

Thank you all for your replies.  And thanks Arnie(dr) for the sample test. 
Here are the results from my workstation:

Loading Data...
Start at: Jul 28 2006  1:39PM
Stop at: Jul 28 2006  1:39PM
Time to Load Data: 3906

BETWEEN Test
Start at: Jul 28 2006  1:39PM
Stop at: Jul 28 2006  1:39PM
Time Difference: 0

Start at: Jul 28 2006  1:39PM
Stop at: Jul 28 2006  1:39PM
Time Difference: 16





Show quoteHide quote

And don't forget the importance of David's comments about handling dates
with BETWEEN.

Show quoteHide quote

Rich (R***@discussions.microsoft.com) writes:
Show quoteHide quoteAnd a difference of 16 is not significant. I usually find when timing
queries that I rarely data between 0 and 16. I may occassionally see 13,
but that's that. As a rule of thumb, I regard anything below 50 as too
inaccurate to be significant.

Spreadsheet totals
Foreign Key may cause multilple cycles
Bulk Insert from Excel
Sort Numbers in Nvarchar As Numbers
How do I filter out unwanted rows by only one different column?