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

Solution 1:

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

Solution 2:

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

Solution 3:

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.

Solution 4:

Hope your specific issue is resolved by now since it is an old post.

Following SET options has potential to affect plan resuse (complete list at the end)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ARITHABORT ON
GO

Following two statements are from msdn - SET ARITHABORT

Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application.

Another interesting topic to understand is Parameter Sniffing as outlined in Slow in the Application, Fast in SSMS? Understanding Performance Mysteries - by Erland Sommarskog

Still another possibility is with conversion (internally) of VARCHAR columns into NVARCHAR while using Unicode input parameter as outlined in Troubleshooting SQL index performance on varchar columns - by Jimmy Bogard

OPTIMIZE FOR UNKNOWN

In SQL Server 2008 and above, consider OPTIMIZE FOR UNKNOWN . UNKNOWN: Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.

OPTION (RECOMPILE)

Use "OPTION (RECOMPILE)" instead of "WITH RECOMPILE" if recompiliing is the only solution. It helps in Parameter Embedding Optimization. Read Parameter Sniffing, Embedding, and the RECOMPILE Options - by Paul White

SET Options

Following SET options can affect plan-reuse, based on msdn - Plan Caching in SQL Server 2008

1. ANSI_NULL_DFLT_OFF 2. ANSI_NULL_DFLT_ON 3. ANSI_NULLS 4. ANSI_PADDING 5. ANSI_WARNINGS 6. ARITHABORT 7. CONCAT_NULL_YIELDS_NUL 8. DATEFIRST 9. DATEFORMAT 10. FORCEPLAN 11. LANGUAGE 12. NO_BROWSETABLE 13. NUMERIC_ROUNDABORT 14. QUOTED_IDENTIFIER