sql runs fast in ssms slow in asp.net

Question

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

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.

You should mark an answer. Most likely you had parameter sniffing issues — mellodev, Jun 14 '12 at 20:32

score 31 · Accepted Answer · answered Apr 16 '12 at 13:52

31

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

answered Apr 16 '12 at 13:52

Oded

489,969
99
883
1,009

1

If you are having the **opposite** problem to the question (in my cast fast in EF Core 5 and slow in SSMS) look at section 3.1 in this linked document and try to create a temporary stored procedure to run your SQL. – Simon_Weaver Feb 26 '21 at 21:23

score 12 · Answer 2 · edited May 23 '17 at 12:00

12

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 SET settings is different. This can contribute to different plans (ultimately this gets attributed to parameter sniffing) and the app side usually ends up worse off.

See these other questions for a lot more details:

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

Procedure times out from ADO.NET but not in SSMS

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

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

edited May 23 '17 at 12:00

Community

1
1

answered Apr 16 '12 at 13:59

Aaron Bertrand

272,866
37
466
490

What is SET settings? – crh225 Nov 07 '16 at 14:57
1

@crh225 Things `SET ARITHABORT`, `SET QUOTED_IDENTIFIER`, etc. https://technet.microsoft.com/en-us/library/ms190707(v=sql.105).aspx – Aaron Bertrand Nov 07 '16 at 20:27

Reza · Answer 3 · 2019-12-11T18:48:18.533

7

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)

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.

edited Dec 11 '19 at 18:48

answered Aug 13 '18 at 14:15

Reza

18,865
13
88
163

This was the solution to my issue, I had no input parameters but the query execution time differed between SSMS and my application. Went from ~7000ms to 1000ms execution time. Thanks! – Henrik Aug 22 '18 at 08:04

score 4 · Answer 4 · edited Mar 17 '21 at 18:51

4

Doing following steps at the same time worked for me.

Try stored procedure recompiling
Clear the plan cache
Updating stats on the DB

edited Mar 17 '21 at 18:51

halfer

19,824
17
99
186

answered Mar 17 '21 at 17:31

Parmar

41
2

score 1 · Answer 5 · answered Jun 25 '13 at 10:44

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.

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.

score 0 · Answer 6 · edited Dec 17 '21 at 08:45

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

implicit conversion which leads to bad plan choice (nvarchar being used instead of varchar). You can observe the NHibernate parameter mapping in its logs.
Lack of index.

NHibernate uses log4net and you just need to add an appender as mentioned here.

score 0 · Answer 7 · answered Apr 28 '21 at 17:31

Table Variables and temp tables make a big difference in the query execution

I am not sure why you are using the table variable you can try this code and see if that solves your slowness mystery

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

sql runs fast in ssms slow in asp.net

7 Answers7

Linked

Related