sql 在 ssms 中运行快在 asp.net 中慢 [英] sql runs fast in ssms slow in asp.net

问题描述

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

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:

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 个产品)多出一倍多.所有三个站点及其数据库都托管在同一台机器上.

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.

推荐答案

您可能遇到了参数嗅探的问题.

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

我建议阅读 Erland Sommarskog 的 应用程序缓慢，SSMS 快速?对问题的理解(文章很长但非常好).

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).

这篇关于sql 在 ssms 中运行快在 asp.net 中慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！