declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
can someone tell me whats going on?
XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>
|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>