Professional Web Applications Themes

Performance difference using "between" in the where clause - Microsoft SQL / MS SQL Server

My question is: which one of these will run quicker? If not (I'm not expecting a definitive answer), what would determine what works best in what situation? What would the speed be dependent on? Where Date between 6/1/03 and 6/15/03 Where Date <= 6/1/03 and date >= 6/15/03...

  1. #1

    Default Performance difference using "between" in the where clause

    My question is: which one of these will run quicker?

    If not (I'm not expecting a definitive answer), what would determine
    what works best in what situation? What would the speed be dependent
    on?

    Where
    Date between 6/1/03 and 6/15/03

    Where
    Date <= 6/1/03
    and date >= 6/15/03
    Jesse O Guest

  2. #2

    Default Re: Performance difference using "between" in the where clause

    There is no performance advantage between these approaches. The optimizer
    will evaluate them in similar manner. Depending on your personal preference
    you can use any of them, however in many cases BETWEEN is considered more
    legible than >= & <= operators.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: Performance difference using "between" in the where clause

    > you can use any of them, however in many cases BETWEEN is considered more
    > legible than >= & <= operators.
    But not as logical. The following implies that all rows from 06/01 at
    midnight to 06/15 11:59:59 PM are included, but we know that is not the
    case:

    BETWEEN '20030601' AND '20030615'

    I really think BETWEEN leads to ambiguity and false assumptions...


    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Performance difference using "between" in the where clause

    Aaron Bertrand - MVP wrote:
    >> you can use any of them, however in many cases BETWEEN is considered
    >> more legible than >= & <= operators.
    >
    > But not as logical. The following implies that all rows from 06/01 at
    > midnight to 06/15 11:59:59 PM are included, but we know that is not
    > the case:
    >
    > BETWEEN '20030601' AND '20030615'
    >
    > I really think BETWEEN leads to ambiguity and false assumptions...
    .... unless the user knows what he's doing. I like BETWEEN myself.

    Bob


    Bob Barrows Guest

  5. #5

    Default Re: Performance difference using "between" in the where clause

    > > I really think BETWEEN leads to ambiguity and false assumptions...
    >
    > ... unless the user knows what he's doing. I like BETWEEN myself.
    Sure, this will always be a potato/potato thing. I just find the following
    more logical AND more legible:

    dtCol >= '20030601' AND dtCol < '20030616'

    Than this:

    dtCol BETWEEN '20030601' AND '20030615 23:59:59.999'

    ....


    Aaron Bertrand - MVP Guest

  6. #6

    Default Re: Performance difference using "between" in the where clause

    Aaron Bertrand - MVP wrote:
    >>> I really think BETWEEN leads to ambiguity and false assumptions...
    >>
    >> ... unless the user knows what he's doing. I like BETWEEN myself.
    >
    > Sure, this will always be a potato/potato thing. I just find the
    > following more logical AND more legible:
    >
    > dtCol >= '20030601' AND dtCol < '20030616'
    >
    > Than this:
    >
    > dtCol BETWEEN '20030601' AND '20030615 23:59:59.999'
    >
    > ...
    tomato ;-)

    Bob


    Bob Barrows Guest

  7. #7

    Default Re: Performance difference using "between" in the where clause

    > Long time ago (about 2 years ) I read from credible source that BETWEEN
    has
    > better performance in some cases.
    What "credible source" is this?

    Internally, BETWEEN is translated to >= and <= anyway, AFAIK.

    I'd love to see an example that shows how one outperforms the other.


    Aaron Bertrand - MVP Guest

Similar Threads

  1. Replies: 3
    Last Post: February 8th, 05:19 AM
  2. What is the difference between command "PS" and "PS -aux"
    By Julie in forum Linux / Unix Administration
    Replies: 3
    Last Post: August 20th, 05:43 AM
  3. Function "Save us" produse a difference between jpg and tif format.
    By broker@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 3
    Last Post: April 30th, 04:41 PM
  4. Difference between "No Cache" and Response.Expires
    By Aaron Bertrand - MVP in forum ASP
    Replies: 0
    Last Post: September 26th, 02:25 PM
  5. Replies: 1
    Last Post: July 21st, 03:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139