sql 在 ssms 中运行快在 asp.net 中慢

sql runs fast in ssms slow in asp.net

提问by Ben

I have been having this problem for a couple of weeks now. The problem is that the query takes 4-5 minutes to run on the website and at most 2 or 3 seconds to run in ssms. Also I found that after I make a change to this query like adding the customerId variable it will start running quickly on the web page but by the next day it is slow again. The query in question is this one:

我已经遇到这个问题几个星期了。问题是查询在网站上运行需要 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

I have the same query running on 2 other site which work just fine. The only difference between the sites is that they are run on different databases and the slow site has a bit more than double the products (54000 products) on it compared to the other 2. All three sites and their databases are hosted on the same machine.

我在其他 2 个站点上运行了相同的查询，它们运行良好。站点之间的唯一区别是它们运行在不同的数据库上，与其他两个站点相比，慢速站点上的产品（54000 个产品）多出一倍多。所有三个站点及其数据库都托管在同一台机器上.

回答by Oded

Chances are that you are hitting on a problem with parameter sniffing.

您可能会遇到参数嗅探的问题。

I suggest reading Slow in the Application, Fast in SSMS?by Erland Sommarskog to get a full understanding of the issue (long article but very good).

我建议阅读应用程序中的慢，SSMS 中的快？作者 Erland Sommarskog 以全面了解该问题（文章很长但非常好）。

回答by Aaron Bertrand

Take a look at sys.dm_exec_sessions for your ASP.Net application and for your SSMS session. I will hazard a guess that at least one of your SETsettings is different. This can contribute to different plans (ultimately this gets attributed to parameter sniffing) and the app side usually ends up worse off.

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

See these other questions for a lot more details:

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

Stored procedure slow when called from web, fast from Management Studio

从 Web 调用时存储过程很慢，从 Management Studio 调用时很快

Procedure times out from ADO.NET but not in SSMS

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

Query times out when executed from web, but super-fast when executed from SSMS

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

ADO .NET vs. SQL Server Management Studio - ADO performs worse

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

回答by Reza

I had same issue, in my case it was related to MARS, so I removed MultipleActiveResultSets=True;from connection string and now running time btween SSMS and asp.net are almost same (0.2s comparing to 4.5s before)

我有同样的问题，在我的情况下它与 MARS 相关，所以我MultipleActiveResultSets=True;从连接字符串中删除，现在 SSMS 和 asp.net 之间的运行时间几乎相同（0.2s 与之前的 4.5s 相比）

Note: MARS = Multiple Active Result Sets. If you set this property on a connection string, you can run multiple queries on the same connection in an interleaved fashion. It's mainly intended to permit you to submit UPDATE statements as you are iterating through a result set.

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

回答by Sharath

Are you using any orm? If you are using nhibernate, you can enable db tracing in nhibernateand see what could be the issue. Following are some of the scenarios that I observed in such scenarios:

你在用orm吗？如果您正在使用nhibernate，您可以启用数据库跟踪nhibernate并查看可能是什么问题。以下是我在此类场景中观察到的一些场景：

1. implicit conversion which leads to bad plan choice (nvarcharbeing used instead of varchar). You can observe the nhibernate parameter mapping in its logs.
2. Lack of index

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

Nhibernateuses log4netand you just need to add an appender as mentioned here: https://devio.wordpress.com/2010/04/14/logging-sql-statements-generated-by-nhibernate/

Nhibernate使用log4net，您只需要添加这里提到的附加程序：https: //devio.wordpress.com/2010/04/14/logging-sql-statements-generated-by-nhibernate/

回答by DJA

For what its worth, very occasionally we run into the same problem; may be once a year. You can spend a good week reading and digesting all of those wonderful resources mentioned in the other answers, or you can do what we do; stop and start SQL Server.

无论如何，我们偶尔会遇到同样的问题；可能是一年一次。您可以花一周时间阅读和消化其他答案中提到的所有精彩资源，或者您可以按照我们的做法进行；停止和启动 SQL Server。

It works a treat.

它是一种享受。

We have noticed that this problem generally occurs after various schema/sp/view mods that may not be directly related to the problem at hand.

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