sql performance problem

I have a database with 24M records in SQL Serv开发者_开发问答er 2000.

When I run this query

select * from cdr
where starttime between '2011-05-15 00:00:00.000' and '2011-05-16 00:00:00.000'

and even this

declare @MinDate char(30) ,@MaxDate char(30)
set @MinDate=substring(convert(char,(getdate()-1), 120),1,10)+' 00:00:00.000'
set @MaxDate=substring(convert(char,(getdate()), 120),1,10)+' 00:00:00.000'
select * from cdr
where starttime between '2011-05-15 00:00:00.000' and @MaxDate

it runs very fast and return 3500 records in firs 10 seconds, note that starttime is char(30) in database

But when I run this query it just return 32 records in 10~60 seconds

declare @MinDate char(30), @MaxDate char(30)

set @MinDate = substring(convert(varchar, (getdate()-1), 120),1,10)+' 00:00:00.000'
set @MaxDate = substring(convert(varchar, (getdate()), 120),1,10)+' 00:00:00.000'

select * from cdr

where starttime between @MinDate and @MaxDate

:: @MinDate value is 2011-05-15 00:00:00.000

Note that starttime is indexed in my database

I want to know what is my problem?

Sql server probably has a query plan cached for the query with 2 parameters that is not optimal for your parameter values. In sqlserver 2008 you can use the Optimize for unknown hint. http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx In SQL server 2000 you can try one of the other options mentioned in the article.

Same issue as here and here. Use OPTION (RECOMPILE)