SP 需要 15 分钟，但执行相同的查询会在 1-2 分钟内返回结果

发布于 2024-07-30 08:41:56 字数 389 浏览 8 评论 0原文

所以基本上我有这个相对较长的存储过程。基本执行流程是，它将一些数据SELECTS INTO放入使用#符号声明的临时表中，然后在这些表中运行游标以生成“运行总计”到第三个临时表是使用 CREATE 创建的。然后，生成的临时表与数据库中的其他表连接，经过一些分组等后生成结果。问题是，这个 SP 一直运行良好，直到现在才在 1-2 分钟内返回结果。现在，突然之间，需要 12 到 15 分钟。如果我从 SP 中提取查询并通过手动设置相同的参数在 Management Studio 中执行它，它将在 1-2 分钟内返回结果，但 SP 需要很长时间。知道会发生什么吗？我尝试生成查询和 SP 的实际执行计划，但由于游标而无法生成。知道为什么 SP 需要这么长时间而查询却不需要这么长时间吗？

原文

分享到QQ

分享到微博

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

发布评论

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

九命猫 2024-08-06 08:41:56

这是参数嗅探的足迹。请参阅此处有关它的另一个讨论； SQL 存储过程执行计划性能不佳 - 参数嗅探

有几种可能的修复方法，包括将WITH RECOMPILE 添加到您的存储过程中，该过程大约可以工作一半的时间。

对于大多数情况，建议的修复方法（尽管这取决于查询和存储过程的结构）是不要直接在查询中使用参数，而是将它们存储到局部变量中，然后在您的疑问。

回复收藏 0 原文

惜醉颜 2024-08-06 08:41:56

这是由于参数嗅探造成的。首先声明临时变量并将传入变量值设置为临时变量，并在整个应用程序中使用临时变量，下面是一个示例。

ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT 
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT * 
FROM   Customers e Where
CustomerId = @customerIdTemp 
End

尝试这种方法

its due to parameter sniffing. first of all declare temporary variable and set the incoming variable value to temp variable and use temp variable in whole application here is an example below.

ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT 
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT * 
FROM   Customers e Where
CustomerId = @customerIdTemp 
End

try this approach

回复收藏 0 原文

忘年祭陌 2024-08-06 08:41:56

尝试重新编译存储过程以放弃任何存储的查询计划

exec sp_recompile 'YourSproc'

然后运行存储过程，注意使用合理的参数。

还比较执行查询的两种方法之间的实际执行计划。

重新计算任何统计数据也可能是值得的。

Try recompiling the sproc to ditch any stored query plan

exec sp_recompile 'YourSproc'

Then run your sproc taking care to use sensible paramters.

Also compare the actual execution plans between the two methods of executing the query.

It might also be worth recomputing any statistics.

回复收藏 0 原文

最笨的告白 2024-08-06 08:41:56

我还会研究参数嗅探。可能是过程需要以稍微不同的方式处理参数。

回复收藏 0 原文

猫九 2024-08-06 08:41:56

我通常通过使用来开始解决此类问题
“打印 getdate() + ' - 步骤 '”。这有助于我缩小最耗时的范围。您可以从查询分析器运行它的位置进行比较，并缩小问题的范围。

回复收藏 0 原文

亂 2024-08-06 08:41:56

我猜这可能是由于缓存。如果运行存储过程两次，第二次会更快吗？

要进一步调查，您可以从 Management Studio 存储过程和查询版本运行它们，并在 Management Studio 中打开显示查询计划选项，然后比较存储过程中哪个区域比作为查询运行时花费的时间更长。

或者，您可以在此处发布存储过程，以便人们提出优化建议。

回复收藏 0 原文

弄潮 2024-08-06 08:41:56

首先，基于使用多个临时表（可以保存在内存中，或者持久化到 tempdb - 无论 SQL Server 决定什么是最好的），听起来 SQL 无论如何都不会执行得很好，并且使用游标。

我的建议是看看是否可以将存储过程重写为基于集合的查询而不是游标方法，这将提供更好的性能并且更容易调整和优化。显然，我不确切知道你的存储过程是做什么的，以表明这对你来说有多容易/可行。

至于为什么 SP 比查询花费的时间更长 - 很难说。当您尝试每种方法时，系统上的负载是否相同？如果在轻负载时运行查询本身，会比在重负载时运行 SP 更好。

此外，为了确保查询确实比 SP 更快，您需要排除数据/执行计划缓存，这使得后续运行的查询更快。您可以使用以下方法清除缓存：

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

但只能在开发/测试数据库服务器上执行此操作，而不是在生产中执行此操作。
然后运行查询，记录统计信息（例如来自探查器）。再次清除缓存。运行 SP 并比较统计数据。

For a start it doesn't sound like the SQL is going to perform too well anyway based on the use of a number of temp tables (could be held in memory, or persisted to tempdb - whatever SQL Server decides is best), and the use of cursors.

My suggestion would be to see if you can rewrite the sproc as a set-based query instead of a cursor-approach which will give better performance and be a lot easier to tune and optimise. Obviously I don't know exactly what your sproc does, to give an indication as to how easy/viable this is for you.

As to why the SP is taking longer than the query - difficult to say. Is there the same load on the system when you try each approach? If you run the query itself when there's a light load, it will be better than when you run the SP during a heavy load.

Also, to ensure the query truly is quicker than the SP, you need to rule out data/execution plan caching which makes a query faster for subsequent runs. You can clear the cache out using:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

But only do this on a dev/test db server, not on production.
Then run the query, record the stats (e.g. from profiler). Clear the cache again. Run the SP and compare stats.

回复收藏 0 原文

彼岸花似海 2024-08-06 08:41:56

1）当您第一次运行查询时，可能需要更多时间。还有一点是，如果您正在使用任何相关的子查询，并且如果您对值进行硬编码，那么它将仅执行一次。当您不对其进行硬编码并通过过程运行它时，如果您尝试从输入值中导出值，则可能需要更多时间。

2) 在极少数情况下，这可能是由于网络流量造成的，而且对于相同的输入数据，我们的查询执行时间也不一致。

回复收藏 0 原文

深海蓝天 2024-08-06 08:41:56

我也遇到了一个问题，我们必须创建一些临时表，然后操作它们必须根据规则计算一些值，最后将计算出的值插入第三个表中。如果将这一切放入单个 SP 中，则需要大约 20-25 分钟。因此，为了进一步优化它，我们将 sp 分成 3 个不同的 sp，现在花费的总时间约为 6-8 分钟。只需确定整个过程中涉及的步骤以及如何将它们分解到不同的 sp 中即可。当然，通过使用这种方法，整个过程所花费的总时间将会减少。

回复收藏 0 原文

流星番茄 2024-08-06 08:41:56

这是因为参数剪切。但如何确认呢？

每当我们想要优化 SP 时，我们都会寻找执行计划。但就您而言，您会看到来自 SSMS 的优化计划，因为仅当它通过代码调用时才会花费更多时间。

对于每个 SP 和 Function，由于 ARITHABORT 选项，SQL Server 会生成两个估计计划。第一个用于 SSMS，第二个用于外部实体（ADO Net）。

ARITHABORT 在 SSMS 中默认处于关闭状态。因此，如果您想检查 SP 从 Code 调用时使用的确切查询计划。

只需在 SSMS 中启用该选项并执行 SP，您将看到 SP 也将从 SSMS 中花费 12-13 分钟。
设置 ARITHABORT 为 ON
EXEC 您的 SpName
SET ARITHABORT OFF

要解决此问题，您只需更新估计查询计划。

有多种方法可以更新估计查询计划。
1.更新表统计信息。
2.重新编译SP
3. 在 SP 中将 ARITHABORT 设置为 OFF，以便它将始终使用为 SSMS 创建的查询计划（不推荐此选项）
有关更多选项，请参阅这篇精彩文章 -
http://www.sommarskog.se/query-plan-mysteries.html