开发者

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");
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜