SQL Server Performance Forum – Threads Archive
Different execution planHi. I’m running SQL Server 2005 and I’m quite new on this platform. I have a query with a subquery on the form select a, b, ..
where x = 1
and mydate = (select max(mydate) from tab where mydate < ‘2007-01-01’) This executes in 6 seconds. If I declare the variables on beforehand like declare @myvar as int
declare @mydate as datetime
set @myvar = 1
set @mydate = ‘2007-01-01’ select a, b, ..
where x = @myvar
and mydate = (select max(mydate) from tab where mydate < @mydate) then I get a different execution plan and the query executes in 19 seconds. How can this happen? Shouldn’t it be the same? Thanks,EB
do a google search on:
sql parameter sniffing its all explain there
Read the following articles/topic/blog… http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611