[thelist] SQL: BETWEEN versus (Greater Than and Less Than)

[Anthony Baratta]

Howdy...

(Hopefully Rudy is still lurking. ;-)

I'm seeing some really strange behaviour on an stored procedure that "was working" fine until a couple of days ago. We have an SP that builds some Dynamic SQL and is then executed using  "sp_executesql @sSQLString". This stored procedure is about 3 - 4 months old  (since the last change to it) and used every day. Suddenly, the web page that activates the SP is timing out because the SP is now taking over a minute to come back with an answer. 

We've tracked it down to one line in the dynamically built SQL, and have "tweaked" it so that it runs under 1 second versus previously 1.5 minutes. But I'm still scratching my head as to "why". The DB size has not changed drastically, and we have not removed any indexes from the target tables. The number of rows returned by this query is only about 100 rows. The part of the SQL that appears to be causing the slow down is within the  WHERE clause and uses the BETWEEN option. Here's the original line:

a.create_dt between 'Mar 19, 2007 12:00AM' and 'Mar 19, 2007 11:59PM'

(the date time stamp is hard coded here for testing, it's a variable otherwise.)

here's the new and improved line:

convert(char(8), create_dt, 112)  >= '20070319' and convert(char(8), create_dt, 112)  <= '20070319'

Why would BETWEEN be so much slower, and why all of a sudden? Granted something has changed - and we are hoping to track that down, but so far nothing. 

We've also run the fully built SQL statement through the Database Tuning Advisor (with SQL Studio  2005) and it recommended an additional index, that was built - no change in the response time. Although the "cost" associated with the Query (calculated via the Execution Plan) went from one clustered index costing 100% to three non-clustered indexes costing 24%, 24% and 12% (the new index recommended was the 12%er). 

Normally I can track the root cause down, but this has me really confused, any ideas are welcome.