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

In my experience the usual reason why a query runs fast in SSMS but slow from .NET is due to differences in the connection's SET-tings. When a connection is opened by either SSMS or SqlConnection, a bunch of SET commands are automatically issued to set up the execution environment. Unfortunately SSMS and SqlConnection have different SET defaults.

One common difference is SET ARITHABORT. Try issuing SET ARITHABORT ON as the first command from your .NET code.

SQL Profiler can be used to monitor which SET commands are issued by both SSMS and .NET so you can find other differences.

The following code demonstrates how to issue a SET command but note that this code has not been tested.

using (SqlConnection conn = new SqlConnection("<CONNECTION_STRING>")) {
    conn.Open();

    using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", conn)) {
        comm.ExecuteNonQuery();
    }

    // Do your own stuff here but you must use the same connection object
    // The SET command applies to the connection. Any other connections will not
    // be affected, nor will any new connections opened. If you want this applied
    // to every connection, you must do it every time one is opened.
}

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

Had the same issue in a test environment, although the live system (on the same SQL server) was running fine. Adding OPTION (RECOMPILE) and also OPTION (OPTIMIZE FOR (@p1 UNKNOWN)) did not help.

I used SQL Profiler to catch the exact query that the .net client was sending and found that this was wrapped with exec sp_executesql N'select ... and that the parameters had been declared as nvarchars - the columns being compared being simple varchars.

Putting the captured query text into SSMS confirmed it runs just as slowly as it does from the .net client.

I found that changing the type of the parameters to AnsiText cleared up the problem:

p = cm.CreateParameter() p.ParameterName = "@company" p.Value = company p.DbType = DbType.AnsiString cm.Parameters.Add(p)

I could never explain why the test and live environments had such marked difference in performance.