d_sysuk (4/29/2008)
There is a big difference in writing :>> Unoptimsed SP method for lookup using local var
create procedure mysum (@myid int)
as
declare @myint int
set @myint= @myid
select x,y,z from mytable
where mylookupfilter = @myint
and:
>>Optimised using input variable allows select statement
>> to be properly cached
create procedure mysum (@myid int)
as
select x,y,z from mytable
where mylookupfilter = @myid
The query plan on those two will be exactly the same. The first one just adds a fraction of a microsecond of allocating some memory for a variable and assigning a value to it, that the second one doesn't have to do. The actual query is the same.
Maybe I'm misunderstanding what you're writing.
Edit: Just to be sure, I just tested both of these concepts on my 100-million row table. Two procs, exact same execution plan, exact same execution time, IO, etc. First run on each took a few seconds, second and subsequent runs on each took too little time to measure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon