Prev: sql server 2008 db ownership question
Next: Return 0 (zero) when null
From: aspfun on 12 Apr 2010 11:27
I found that difference between 1) and 2). 1) is faster than 2). Why? How to
make 2) faster?

1)
select * into #mytemp from order where OrderID='123456'
2)
decalre @orderid nvarchar(20)
set @orderid = '123456'
select * into #mytemp from order where OrderID=(a)orderid
From: Plamen Ratchev on 12 Apr 2010 12:05
Compare the execution plans of both queries and that will answer your questions. In the first query using a literal will
result in auto-parameterization and generating efficient plan for the predicate value. In the second query using a local
variable prevents SQL Server to "see" the value and forces using statistics to generate query plan. That may or may not
generate efficient query plan.

Read more details here:
http://technet.microsoft.com/en-us/library/cc966425.aspx

Add OPTION (RECOMPILE) to the end of the second query and see if that helps with performance.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: sql server 2008 db ownership question
Next: Return 0 (zero) when null