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

Question

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 = Configuration.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; 

Answer 1

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 

Answer 2

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