Query Extremely Slow in Code But 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

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?

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?

Why my query runs fast on mgmt studio but slow in application for specific parameters?

The links in the comments are great: 1)Slow in the Application, Fast in SSMS and 2)The Elephant and the Mouse, or, Parameter Sniffing in SQL Server

Not that I understood all those; but I got rid of this by converting that dynamic SQL into a stored procedure which uses index hint.

Query fast, but when in a VIEW, it's slow - due to ROW_NUMBER

1) Here ROW_NUMBER applies to filtered data only:

SELECT ROW_NUMBER(), ... FROM MyTables WHERE PersonID = x

At first it filters by PersonID, then it computes ROW_NUMBER

2) Here ROW_NUMBER applies to all of the data:

CREATE VIEW MyView as
  select ROW_NUMBER(), ... FROM MyTables

SELECT * FROM MyView WHERE PersonID = x

and only after proceeding full data the filter by PersonID is applied

it's the same as

SELECT * FROM
(SELECT ROW_NUMBER(), ... FROM MyTables
) t
WHERE t.PersonID = x

check out the example:

GO
CREATE VIEW dbo.test_view
AS
    SELECT ROW_NUMBER() OVER (ORDER BY NAME) rn, o.name, o.[object_id]
    FROM sys.objects o
GO
SET SHOWPLAN_XML ON
GO
SELECT rn, o.name, o.[object_id] FROM dbo.test_view o
WHERE OBJECT_ID < 100
GO
SELECT ROW_NUMBER() OVER (ORDER BY NAME) rn, o.name, o.[object_id] FROM sys.objects o
WHERE OBJECT_ID < 100
GO
SET SHOWPLAN_XML OFF
GO
DROP VIEW dbo.test_view
GO

With the view filter operation is in the very end. So plans are different actually.

---

Related Topics