SQL Runs Fast in Ssms Slow in ASP.NET

sql runs fast in ssms slow in asp.net

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

SQL Query that runs fine in SSMS runs very slow in ASP.NET

There are all kinds of things that could be going on.

First, Ivan G. is right that connection parameters and SET options might be different between SSMS and your ASP.NET client. That's something worth looking into in Profiler, if you have access to it.

Second, if you've run your query multiple times in a row in SSMS, it's possible the results are being cached and that's why it runs so fast in SSMS. If it runs slowly the first time you open up SSMS and try to run it, but then speeds up, that's a sign there's caching going on.

As for why adding one extra clause to a join could slow things down, it's hard to say why without knowing more about your tables, but it's not impossible that that could have done it. Is there an index over BATCH_INGR that includes both FACTORY and INGR_CODE? You might need one now that you're including INGR_CODE in your join conditions.

The best way to find out is to look at the query plan with and without the INGR_CODE clause and see how it differs. Is the cost figure for one query bigger than for the other? Are there table scans where there weren't before? Has an index seek turned into an index scan?

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

I've had a similar issue arise in the past, so I'm eager to see a resolution to this question. Aaron Bertrand's comment on the OP led to Query times out when executed from web, but super-fast when executed from SSMS, and while the question is not a duplicate, the answer may very well apply to your situation.

In essence, it sounds like SQL Server may have a corrupt cached execution plan. You're hitting the bad plan with your web server, but SSMS lands on a different plan since there is a different setting on the ARITHABORT flag (which would otherwise have no impact on your particular query/stored proc).

See ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException for another example, with a more complete explanation and resolution.

Query runs fast in Query Analyzer but slow in C# application

Usually these are SET setting differences, leading to different plans. In Management Studio the procedure is probably running with the same settings as were in place when it was created.

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
• sql runs fast in ssms slow in asp.net

And also read Erland Sommarskog's article, Slow in the Application, Fast in SSMS?

Query extremely slow in code but fast in SSMS

Your code in SSMS is not the same code you run in your application. This line in your application adds a NVARCHAR parameter:

 ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);

while in the SSMS script you declare it as VARCHAR:

declare @clientID varchar(200)

Due to the rules of Data Type Precedence the Where client_id = @clientID expression in your query is not SARG-able where @clientID is of type NVARCHAR (I'm making a leap of faith and assume that client_id column is of type VARCHAR). The application thus forces a table scan where the SSMS query can do a quick key seek. This is a well know and understood issue with using Parameters.AddWithValue and has been discussed in many articles before, eg. see How Data Access Code Affects Database Performance. Once the problem is understood, the solutions are trivial:

• add parameters with the constructor that accepts a type: Parameters.Add("@clientID", SqlDbType.Varchar, 200) (and do pass in the explicit length to prevent cache pollution, see Query performance and plan cache issues when parameter length not specified correctly

• or cast the parameter in the SQL text: where client_id = cast(@clientID as varchar(200)).

The first solution is superior because it solves the cache pollution problem in addition to the SARG-ability problem.

I would also recommend you read Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

SQL Server query runs slower from ADO.NET than in SSMS

If a bad plan is cached then the same bad plan should be used from SSMS too, if you run the very same query with identical arguments.

There cannot be better solution that finding the root cause. Trying to peek and poke various settings in the hope it fixes the problem will never give you the confidence it is actually fixed. Besides, next time the system may have a different problem and you'll believe this same problem re-surfaced and apply a bad solution.

The best thing to try is to capture the bad execution plan. Showplan XML Event Class Profiler event is your friend, you can get the plan of the ADO.Net call. This is a very heavy event, so you should attach profiler and capture it only when the problem manifests itself, in a short session.

Query IO statistics can also be of help. RPC:Completed and SQL: Batch Completed events both include Reads and Writes so you can compare the amount of logical IO performed by ADO.Net invocation vs. SSMS one. Large difference (for exactly the same query and params) indicate different plans.sys.dm_exec_query_stats is another avenue of investigation. You can find your query plan(s) in there and inspect the execution stats.

All these should help establish with certitude if the problem is a bad plan or something else, to start with.

---

Related Topics