SQL Query Slow in .Net Application But Instantaneous in SQL Server Management Studio

SQL Query slow in .NET application but instantaneous in SQL Server Management Studio

If this is parameter sniffing, try to add option(recompile) to the end of your query.
I would recommend creating a stored procedure to encapsulate logic in a more manageable way. Also agreed - why do you pass 5 parameters if you need only three, judging by the example?
Can you use this query instead?

select TrustAccountValue from
(
 SELECT MAX (tal.trustaccountlogid), tal.TrustAccountValue
 FROM  TrustAccountLog AS tal
 INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
 INNER JOIN Users usr ON usr.UserID = ta.UserID
 WHERE usr.UserID = 70402 AND
 ta.TrustAccountID = 117249 AND
 tal.TrustAccountLogDate < '3/1/2010 12:00:00 AM'
 group by tal.TrustAccountValue
) q

And, for what it's worth, you are using ambiguous date format, depending on the language settings of the user executing query. For me for example, this is 3rd of January, not 1st of March. Check this out:

set language us_english
go
select @@language --us_english
select convert(datetime, '3/1/2010 12:00:00 AM')
go
set language british
go
select @@language --british
select convert(datetime, '3/1/2010 12:00:00 AM')

The recommended approach is to use 'ISO' format yyyymmdd hh:mm:ss

select convert(datetime, '20100301 00:00:00') --midnight 00, noon 12

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 SSMS VS .Net Perfomance Query

OK,
seems i found the answer in this POST

Same query with the same query plan takes ~10x longer when executed from ADO.NET vs. SMSS

MultipleActiveResultsSet option in ado.net ConnectionString should be FALSE

and @DanGuzman thank you for clearing this. "SSMS also uses ADO.NET."

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 times out from web app but runs fine from management studio

This is what I've learned so far from my research.

.NET sends in connection settings that are not the same as what you get when you log in to management studio. Here is what you see if you sniff the connection with Sql Profiler:

-- network protocol: TCP/IP  
set quoted_identifier off  
set arithabort off  
set numeric_roundabort off  
set ansi_warnings on  
set ansi_padding on  
set ansi_nulls off  
set concat_null_yields_null on  
set cursor_close_on_commit off  
set implicit_transactions off  
set language us_english  
set dateformat mdy  
set datefirst 7  
set transaction isolation level read committed  

I am now pasting those setting in above every query that I run when logged in to sql server, to make sure the settings are the same.

For this case, I tried each setting individually, after disconnecting and reconnecting, and found that changing arithabort from off to on reduced the problem query from 90 seconds to 1 second.

The most probable explanation is related to parameter sniffing, which is a technique Sql Server uses to pick what it thinks is the most effective query plan. When you change one of the connection settings, the query optimizer might choose a different plan, and in this case, it apparently chose a bad one.

But I'm not totally convinced of this. I have tried comparing the actual query plans after changing this setting and I have yet to see the diff show any changes.

Is there something else about the arithabort setting that might cause a query to run slowly in some cases?

The solution seemed simple: Just put set arithabort on into the top of the stored procedure. But this could lead to the opposite problem: change the query parameters and suddenly it runs faster with 'off' than 'on'.

For the time being I am running the procedure 'with recompile' to make sure the plan gets regenerated each time. It's Ok for this particular report, since it takes maybe a second to recompile, and this isn't too noticeable on a report that takes 1-10 seconds to return (it's a monster).

But it's not an option for other queries that run much more frequently and need to return as quickly as possible, in just a few milliseconds.

Entity Framework query slow, but same SQL in SqlQuery is fast

Found it. It turns out it's an issue of SQL data types. The SomeStringProp column in the database was a varchar, but EF assumes that .NET string types are nvarchars. The resulting translation process during the query for the DB to do the comparison is what takes a long time. I think EF Prof was leading me astray a bit here, a more accurate representation of the query being run would be the following:

 SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
 ...
 FROM [MyTable] as [Extent1]
 WHERE [Extent1].[SomeStringProp] = N'1234567890'

So the resulting fix is to annotate the code-first model, indicating the correct SQL data type:

public class MyTable
{
    ...

    [Column(TypeName="varchar")]
    public string SomeStringProp { get; set; }

    ...
}

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

---

Related Topics