SQL Server sp_ExecuteSQL 和执行计划

发布于 2024-09-19 03:55:40 字数 124 浏览 5 评论 0原文

我有一个查询，它在 SQL Server Management Studio 中运行得非常快，但在 sp_ExecuteSQL 下运行时运行得非常慢。

这是否与在 sp_ExecuteSQL 下运行时未缓存执行计划有关？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤檠 2024-09-26 03:55:40

否。

您可以查看两个执行计划并使用以下查询对它们进行比较。

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%Some unique string in your query%' 
                                          and attribute='set_options'

sp_executesql 版本的 objtype 为“prepared”

No.

You can see both execution plans and compare them using the following query.

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%Some unique string in your query%' 
                                          and attribute='set_options'

The sp_executesql version will have an objtype of "prepared"

回复收藏 0 原文

赠意 2024-09-26 03:55:40

经历过同样的行为。（设置选项等于）
常规查询生成并行计划并使用 sp_executesql 生成串行计划。

declare @xyzParam1 datetime,@xyzParam2 datetime
select @xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'
SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2
;

 exec sp_executesql N'SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2',N'@xyzParam1 datetime,@xyzParam2 datetime',@xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'

我设法通过修改使用的视图获得最佳结果，因为它包含例如始终预期的数据的左连接。（转换为 INNER join ）

现在，常规查询选择与使用 sp_executesql 获得的计划相同的计划，并且性能要好得多。

Experienced the same behaviour. ( set options equal )
Regular Query producing parallel plan and using sp_executesql it produced a serial plan.

declare @xyzParam1 datetime,@xyzParam2 datetime
select @xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'
SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2
;

 exec sp_executesql N'SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2',N'@xyzParam1 datetime,@xyzParam2 datetime',@xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'

I managed to obtain an optimal result modifying the used view because it contained e.g. left joins for data that was always expected. ( converted to INNER join )

Now the regular query picks the same plan as the one obtained using sp_executesql and performance is way better.

回复收藏 0 原文