.NET应用程序中的SQL查询速度较慢，但​​在SQL Server Management

Question

这是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'

)

基本上，有一个Users表，一个TrustAccount表和一个TrustAccountLog表。

用户：包含用户及其详细信息

TrustAccount：一个用户可以有多个TrustAccounts。

TrustAccountLog：包含对所有TrustAccount“运动”的审核。一个

TrustAccount与多个TrustAccountLog条目关联。现在，此查询在SQL Server Management Studio中以毫秒为单位执行，但是由于某些奇怪的原因，它在我的C＃应用程序中永久占用时间，有时甚至超时（120s）。

简而言之，这是代码。它在循环中被多次调用，并且语句已准备好。

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;

// 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;