Execution Plan reuse
Consider the following "code"
define stmt1 = 'insert into T(a, b) values(1, 1);
define stmt2 = 'select * from T';
MSSqlCommand.Execute( stmt1;stmt2 );
MSSqlCommand.Execute( stmt2 );
Investigating the cached query-plans using:
SELECT [cp].[refcounts]
, [cp].[usecou开发者_JS百科nts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;
My impression is that the first "Execute" generates a composite execution plan instead of two singular execution plans, thereby disabling the second "Execute" reusing any execution plan generated in the first Execute.
Am I right?
Yes, you're right. To reuse the the second part of the execution plan you need to split the first statement into 2 separate execution plans. You can do this either by executing them with separate MSSqlCommand.Execute
calls or by using two calls to sp_executesql
in one query (this adds one level of indirection). It would look something like this (in pseudocode):
MSSqlCommand.Execute('exec sp_executesql stmt1; exec sp_executesql stmt2");
精彩评论