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

Here is the SQL

SELECT 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.trustaccountlogid =  
(
 SELECT MAX (tal.trustaccountlogid)
 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'
)

Basicaly there is a Users table a TrustAccount table and a TrustAccountLog table.

Users: Contains users and their details

TrustAccount: A User can have multiple TrustAccounts.

TrustAccountLog: Contains an audit of all TrustAccount "movements". A

TrustAccount is associated with multiple TrustAccountLog entries. Now this query executes in milliseconds inside SQL Server Management Studio, but for some strange reason it takes forever in my C# app and even timesout (120s) sometimes.

Here is the code in a nutshell. It gets called multiple times in a loop and the statement gets prepared.

cmd.CommandTimeout = Configur开发者_C百科ation.DBTimeout;
cmd.CommandText = @"SELECT 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 = @UserID1 AND 
ta.TrustAccountID = @TrustAccountID1 AND 
tal.trustaccountlogid =  
(
    SELECT MAX (tal.trustaccountlogid) 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 = @UserID2 AND 
    ta.TrustAccountID = @TrustAccountID2 AND 
    tal.TrustAccountLogDate < @TrustAccountLogDate2 
)";
cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountLogDate2", SqlDbType.DateTime).Value =TrustAccountLogDate;

// And then...

reader = cmd.ExecuteReader();
if (reader.Read())
{
   double value = (double)reader.GetValue(0);
   if (System.Double.IsNaN(value))
      return 0;
   else
      return value;
}
else
   return 0;

---

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.

---

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

---

Most likely the problem lies in the criterion

tal.TrustAccountLogDate < @TrustAccountLogDate2

The optimal execution plan will be highly dependent on the value of the parameter, passing 1910-01-01 (which returns no rows) will most certainly cause a different plan than 2100-12-31 (which returns all rows).

When the value is specified as a literal in the query, SQL server knows which value to use during plan generation. When a parameter is used, SQL server will generate the plan only once and then reuse it, and if the value in a subsequent execution differs too much from the original one, the plan will not be optimal.

To remedy the situation, you can specify OPTION(RECOMPILE) in the query. Adding the query to a stored procedure won't help you with this particular issue, unless you create the procedure WITH RECOMPILE.

Others have already mentioned this ("parameter sniffing"), but I thought a simple explanation of the concept won't hurt.

---

It might be type conversion issues. Are all the IDs really SqlDbType.Int on the data tier?

Also, why have 4 parameters where 2 will do?

cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId;

Could be

cmd.Parameters.Add("@TrustAccountID", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userId;

Since they are both assigned the same variable.

(This might be causing the server to make a different plan since it expects four different variables as op. to. 4 constants - making it 2 variables could make a difference for the server optimization.)

---

Sounds possibly related to parameter sniffing? Have you tried capturing exactly what the client code sends to SQL Server (Use profiler to catch the exact statement) then run that in Management Studio?

Parameter sniffing: SQL poor stored procedure execution plan performance - parameter sniffing

I haven't seen this in code before, only in procedures, but it's worth a look.

---

In my case the problem was that my Entity Framework was generating queries that use exec sp_executesql.

When the parameters don't exactly match in type the execution plan does not use indexes because it decides to put the conversion into the query itself. As you can imagine this results in a much slower performance.

in my case the column was defined as CHR(3) and the Entity Framework was passing N'str' in the query which cause a conversion from nchar to char. So for a query that looks like this:

ctx.Events.Where(e => e.Status == "Snt")

It was generating an SQL query that looks something like this:

FROM [ExtEvents] AS [Extent1] ... WHERE (N''Snt'' = [Extent1].[Status]) ...

The easiest solution in my case was to change the column type, alternatively you can wrestle with your code to make it pass the right type in the first place.

---

Since you appear to only ever be returning the value from one row from one column then you can use ExecuteScalar() on the command object instead, which should be more efficient:

    object value = cmd.ExecuteScalar();

    if (value == null)
        return 0;
    else
        return (double)value;

---

I had this problem today and this solve my problem: https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/

I put on the begining of my SP this: Set ARITHABORT ON

Holp this help you!

---

You don't seem to be closing your data reader - this might start to add up over a number of iterations...

---

I had a problem with a different root cause that exactly matched the title of this question's symptoms.

In my case the problem was that the result set was held open by the application's .NET code while it looped through every returned record and executed another three queries against the database! Over several thousand rows this misleadingly made the original query look like it had been slow to complete based on timing information from SQL Server.

The fix was therefore to refactor the .NET code making the calls so that it doesn't hold the result set open while processing each row.

---

I realise the OP doesn't mention the use of stored procedures but there is an alternative solution to parameter sniffing issues when using stored procedures that is less elegant but has worked for me when OPTION(RECOMPILE) doesn't appear to do anything.

Simply copy your parameters to variables declared in the procedure and use those instead.

Example:

ALTER PROCEDURE [ExampleProcedure]
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN

--reassign to local variables to avoid parameter sniffing issues
DECLARE @MyStartDate datetime,
        @MyEndDate datetime

SELECT 
    @MyStartDate = @StartDate,
    @MyEndDate = @EndDate

--Rest of procedure goes here but refer to @MyStartDate and @MyEndDate
END

---

I have just had this exact issue. A select running against a view that returned a sub second response in SSMS. But run through sp_executesql it took 5 to 20 seconds. Why? Because when I looked at the query plan when run through sp_executesql it did not use the correct indexes. It was also doing index scans instead of seeks. The solution for me was simply to create a simple sp that executed the query with the passed parameter. When run through sp_executesql it used the correct indexes and did seeks not scans. If you want to improve it even further make sure to use command.CommandType = CommandType.StoredProcedure when you have a sp then it does not use sp_executesql it just uses EXEC but this only shaved ms off the result.

This code ran sub second on a db with millions of records

   public DataTable FindSeriesFiles(string StudyUID)
    {
        DataTable dt = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("VNA.CFIND_SERIES", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@StudyUID", StudyUID);
               using (SqlDataReader reader = command.ExecuteReader())
                {
                    dt.Load(reader);
                }
                return dt;
            }
        }
    }

Where the stored procedure simply contained

CREATE PROCEDURE [VNA].[CFIND_SERIES]
    @StudyUID NVARCHAR(MAX)
AS BEGIN
    SET NOCOUNT ON
    SELECT * 
    FROM CFIND_SERIES_VIEW WITH (NOLOCK) 
    WHERE [StudyInstanceUID] = @StudyUID
    ORDER BY SeriesNumber
END

This took 5 to 20 seconds (but the select is exactly the same as the contents of the VNA.CFIND_SERIES stored procedure)

public DataTable FindSeriesFiles(string StudyUID)
    {
        DataTable dt = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText =" SELECT * FROM CFIND_SERIES_VIEW WITH (NOLOCK) WHERE StudyUID=@StudyUID ORDER BY SeriesNumber";
                command.Parameters.AddWithValue("@StudyUID", StudyUID);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    dt.Load(reader);
                }
                return dt;
            }
        }
    }

---

I suggest you try and create a stored procedure - which can be compiled and cached by Sql Server and thus improve performance