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

SQL poor stored procedure execution plan performance - parameter sniffing

有几种可能的修复方法,包括使用“重新编译”添加到存储过程中,这大约占了一半的时间。

对于大多数情况(尽管这取决于查询和存储过程的结构),建议的修复方法是

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

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

试试这个方法

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

exec sp_recompile 'YourSproc'

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

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

我还将研究参数嗅探。可能是proc需要以不同的方式处理参数slighlty。

我通常使用 “打印getdate()+”-步骤“”。这有助于我缩小花费最多时间的范围。您可以从查询分析器运行它的位置进行比较,并缩小问题所在的范围。

要进一步调查,您可以在management studio中打开“显示查询计划”选项的情况下,从management studio存储过程和查询版本中运行它们,然后比较存储过程中哪个区域比作为查询运行时花费的时间长。

另外,您也可以在这里发布存储过程,以便人们提出优化建议。

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

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

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

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

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

1) 第一次运行查询时,可能需要更多时间。还有一点是,如果您使用的是任何corelated子查询,并且如果您对值进行硬编码,那么它将只执行一次。如果您没有对其进行硬编码并在整个过程中运行它,并且如果您试图从输入值派生值,则可能需要更多的时间。

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

这是因为参数截断。但是你怎么能证实呢?

对于每个SP和函数,SQL server都会生成两个估计的计划,因为有arithaport选项。一个用于SSM,另一个用于外部实体(ADO Net)。

默认情况下,SSMS中的ARITHABORT处于关闭状态。所以,如果您想检查SP在从代码调用时使用的确切查询计划。

只需在SSMS中启用该选项并执行您的SP,您将看到SP也将从SSMS花费12-13分钟。 启用ARITHABORT 执行你的名字 抵消阿里沙波特

1.更新表统计信息。 2.重新编译SP http://www.sommarskog.se/query-plan-mysteries.html

我认为这个问题与temp表的类型(前缀)有关。此临时表保存该数据库会话的数据。当您在应用程序中运行临时表时,临时表将被删除并重新创建。