Yash (ya****@gmail.com) writes:
I don't think one query runs faster because the plan is cached. The
numbers I have given are what I see after multiple executions.
I have now tested with 2 scenarios, one with INSERT and the other
without. This time both use sp_executesql and have parameters. I
observe that the one with :
insert into #bacs_report
EXEC sp_executesql <query with parameters>
takes 60K reads
and the one with just
EXEC sp_executesql <query with parameterstakes 18K
Looks like the INSERT is causing the problem when used with
sp_executesql. Why would the plan differ in the 2 cases?
I ran this simple test:
create table #ord(OrderID int)
go
exec sp_executesql
N'SELECT OrderID FROM Northwind..Orders WHERE CustomerID = @c',
N'@c nchar(5)', @c = N'ALFKI'
* go
insert #ord
exec sp_executesql
N'SELECT OrderID FROM Northwind..Orders WHERE CustomerID = @c',
N'@c nchar(5)', @c = N'ALFKI'
go
I get 16 reads for the plain SELECT, where as the SELECT as part of the
INSERT gets 24 reads, and the INSERT batch in total has 63.
I think part of the problem here is what is a read. More IO operations
are to be the expected with the INSERT, but that would be writes. But
I don't see any writes at all, so it seems that writes counts as reads
for some reason.
I'm guessing wildly here, but I think the extra reads for the SELECT
insert the INSERT comes from when result is written to some internal
rowset.
Another issuse is that SQL Query Analyzer does not show the query plan
when the INSERT stmt is present with sp_executesql.
You can catch the plans with the Performance:Show Statistics Profile
event. You need to include BinaryData in the result set.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Links for SQL Server Books Online:
SQL 2008:
http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:
http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx