I ran a test here on a 2 million row table running several hundred iterations per run. I found no statistical significance for using "between" versus "<>" operators. (p=0.626) Indexed or not doesn't seem to matter either. I would bet that most query parsers/planners basically turn the between in a >= <= in the end run, so they're basically the same.
My Run Results (unindexed=50 runs, indexed=1000 runs): Indexed BetweenOrLessGreater Milliseconds 0 1 32750 0 1 24936 0 1 24690 0 1 32686 0 1 20766 0 1 28923 0 1 26643 0 1 24013 0 1 24516 0 1 21486 0 1 25593 0 1 30736 0 1 20330 0 1 20436 0 1 20376 0 1 20610 0 1 20360 0 1 20360 0 1 20343 0 1 20406 0 0 20860 0 0 20470 0 0 20326 0 0 20376 0 0 23156 0 0 30733 0 0 34176 0 0 37406 0 0 36376 0 0 36236 0 0 28500 0 0 20906 0 0 20826 0 0 20940 0 0 20406 0 0 23860 0 0 22203 0 0 30203 0 0 20360 0 0 30156 1 0 9780 1 0 9783 1 0 9780 1 0 10126 1 0 9796 1 0 9783 1 0 9826 1 0 9813 1 0 9766 1 0 9780 1 0 9766 1 0 9766 1 0 9780 1 0 10143 1 0 9780 1 0 9783 1 0 9826 1 0 9826 1 0 9783 1 0 9813 1 1 9796 1 1 9796 1 1 9796 1 1 9830 1 1 9796 1 1 9796 1 1 9796 1 1 9796 1 1 9830 1 1 9826 1 1 9846 1 1 9796 1 1 9796 1 1 9813 1 1 9813 1 1 9810 1 1 9800 1 1 9826 1 1 10140 1 1 9783 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2139 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
