SQL 在 SSMS 中运行速度很快，asp.net 速度很慢

Question

我已经遇到这个问题几个星期了。问题是查询在网站上运行需要 4-5 分钟，在 ssms 中运行最多需要 2 或 3 秒。我还发现，在我对此查询进行更改（例如添加 customerId 变量）后，它将开始在网页上快速运行，但到第二天它又变慢了。有问题的查询是这样的：

DECLARE @customerID INT
SET @customerID = @CustID
DECLARE @MyTable table(
Iden int NOT NULL IDENTITY(1,1),
ProductID int)
INSERT INTO @MyTable(ProductID)
SELECT P.ProductID FROM Product P WITH (NOLOCK)
left join Compunix_ProductMMY cpmmy with (nolock) on p.ProductID = cpmmy.ProductID
left join Compunix_CustomerMMY ccmmy with (nolock) on ccmmy.mmyid = cpmmy.mmyid
WHERE P.Deleted=0 AND P.Published=1 and (ccmmy.customerid = @customerID OR cpmmy.productid IS NULL)
SELECT c.Name, c.SeName, c.CategoryID
FROM Category c WITH (NOLOCK) 
JOIN ProductCategory PC With (NOLOCK) ON C.CategoryID = PC.CategoryID
JOIN @MyTable MT ON PC.ProductID=MT.ProductID
WHERE C.Published = 1
GROUP BY c.Name, c.SeName, c.CategoryID
ORDER BY c.Name

我在另外 2 个站点上运行了相同的查询，这些查询工作正常。站点之间的唯一区别是它们在不同的数据库上运行，与其他 54000 个站点相比，慢速站点上的产品（54000 个产品）是其他 2 个站点的两倍多。所有三个站点及其数据库都托管在同一台计算机上。

Answer 1

很可能您遇到了参数嗅探问题。

我建议阅读Erland Sommarskog的Slow in the Application， Fast in SSMS？，以全面了解这个问题（长文章，但非常好）。

Answer 2

查看 ASP.Net 应用程序和 SSMS 会话的sys.dm_exec_sessions。我冒昧地猜测，您的SET设置中至少有一个是不同的。这可能会导致不同的计划（最终这归因于参数嗅探），并且应用程序端通常最终会变得更糟。

有关更多详细信息，请参阅以下其他问题：

从 Web 调用时存储过程速度慢，从管理工作室调用时速度快

过程从 ADO.NET 超时，但不在 SSMS 中超时

从 Web 执行查询时超时，但从 SSMS 执行时速度超快

ADO .NET 与.SQL Server Management Studio - ADO 性能更差

Answer 3

我遇到了同样的问题，就我而言，它与 MARS 有关，所以我从连接字符串中删除了MultipleActiveResultSets=True;，现在运行时间补间 SSMS 和 asp.net 几乎相同（0.2 秒，而之前为 4.5 秒）

注意：MARS = 多个活动结果集。如果在连接字符串上设置此属性，则可以以交错方式对同一连接运行多个查询。它主要用于允许您在循环访问结果集时提交 UPDATE 语句。

Answer 4

同时执行以下步骤对我有用。

1. 尝试存储过程重新编译
2. 清除计划缓存
3. 更新数据库上的统计信息

Answer 5

对于它的价值，我们偶尔会遇到同样的问题; 可能是一年一次。 您可以花一周的时间阅读和消化其他答案中提到的所有精彩资源，或者您可以做我们所做的;停止并启动 SQL Server。

它是一种享受。

我们注意到，这个问题通常发生在各种模式/sp/view mod之后，这些模式/sp/view mod 可能与手头的问题没有直接关系。

Answer 6

表变量和临时表对查询执行有很大的影响

我不确定你为什么使用表变量，你可以试试这段代码，看看这是否能解决你的缓慢之谜

DECLARE @customerID INT
SET @customerID = @CustID
SELECT c.Name, c.SeName, c.CategoryID
FROM Category c WITH (NOLOCK) 
JOIN ProductCategory PC With (NOLOCK) ON C.CategoryID = PC.CategoryID
JOIN (SELECT P.ProductID FROM Product P WITH (NOLOCK)
        left join Compunix_ProductMMY cpmmy with (nolock) on p.ProductID = cpmmy.ProductID
        left join Compunix_CustomerMMY ccmmy with (nolock) on ccmmy.mmyid = cpmmy.mmyid
        WHERE P.Deleted=0 AND P.Published=1 and (ccmmy.customerid = @customerID OR cpmmy.productid IS NULL)
        ) MT ON PC.ProductID=MT.ProductID
WHERE C.Published = 1
GROUP BY c.Name, c.SeName, c.CategoryID
ORDER BY c.Name

Answer 7

您是否使用任何ORM？如果您使用的是 NHibernate，则可以在 NHibernate 中启用数据库跟踪，并查看可能出现的问题所在。以下是我在此类场景中观察到的一些场景：

1. 隐式转换，导致错误的计划选择（nvarchar使用而不是varchar）。您可以在其日志中观察 NHibernate 参数映射。
2. 缺少索引。

NHibernate使用log4net，你只需要添加一个附加器，如这里提到的那样。