SQL Server Performance Forum – Threads Archive

Different execution plan

Hi. 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, ..
from tab
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, ..
from tab
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
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E6TAE
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>