SQL查询在.NET应用程序缓慢，但在瞬时SQL Server Management Studio中 [英] SQL Query slow in .NET application but instantaneous in SQL Server Management Studio

问题描述

下面是SQL

  SELECT tal.TrustAccountValue
从TrustAccountLog AS TAL
INNER JOIN TrustAccount的助教ta.TrustAccountID = tal.TrustAccountID
INNER JOIN用户USR ON usr.UserID = ta.UserID
WHERE usr.UserID = 70402 AND
ta.TrustAccountID = 117249 AND
tal.trustaccountlogid =
（
 SELECT MAX（tal.trustaccountlogid）
 从TrustAccountLog AS TAL
 INNER JOIN TrustAccount的助教ta.TrustAccountID = tal.TrustAccountID
 INNER JOIN用户USR ON usr.UserID = ta.UserID
 WHERE usr.UserID = 70402 AND
 ta.TrustAccountID = 117249 AND
 tal.TrustAccountLogDate＆LT; 3/1/2010 12:00:00 AM
）
 

Basicaly有一个用户表TrustAccount表和TrustAccountLog表。结果
用户：包含用户和他们的详细资料结果
TrustAccount：用户可以有多个TrustAccounts结果。
TrustAccountLog：包含所有TrustAccount运动进行了审计。一个结果TrustAccount与多个TrustAccountLog项关联。
现在，这个查询执行的SQL Server Management Studio中毫秒，但是由于一些奇怪的原因，它在我的C＃应用​​程序永远需要，甚至timesout（120秒）的时候。

下面是一个简单地说了code。它得到一个循环多次调用和语句获取prepared。

  cmd.CommandTimeout = Configuration.DBTimeout;
cmd.CommandText =SELECT tal.TrustAccountValue FROM TrustAccountLog AS TAL INNER JOIN TrustAccount的助教ta.TrustAccountID = tal.TrustAccountID INNER JOIN用户USR ON usr.UserID = ta.UserID WHERE usr.UserID = @ UserID1 AND ta.TrustAccountID = @ TrustAccountID1和tal.trustaccountlogid =（SELECT MAX（tal.trustaccountlogid）FROM TrustAccountLog AS TAL INNER JOIN TrustAccount的助教ta.TrustAccountID = tal.TrustAccountID INNER JOIN用户USR ON usr.UserID = ta.UserID WHERE usr.UserID = @ UserID2 AND ta.TrustAccountID = @ TrustAccountID2和tal.TrustAccountLogDate＆LT; @ TrustAccountLogDate2））;
cmd.Parameters.Add（@ TrustAccountID1，SqlDbType.Int）.value的= trustAccountId;
cmd.Parameters.Add（@ UserID1，SqlDbType.Int）.value的=用户id;
cmd.Parameters.Add（@ TrustAccountID2，SqlDbType.Int）.value的= trustAccountId;
cmd.Parameters.Add（@ UserID2，SqlDbType.Int）.value的=用户id;
cmd.Parameters.Add（@ TrustAccountLogDate2，SqlDbType.DateTime）.value的= TrustAccountLogDate;// 接着...读者= cmd.ExecuteReader（）;
如果（reader.Read（））
{
   双值=（双）reader.GetValue（0）;
   如果（System.Double.IsNaN（值））
      返回0;
   其他
      返回值;
}
其他
   返回0;
 

解决方案

如果这个参数嗅探，尽量选项（重新编译）添加到您的查询的末尾。
我建议创建存储过程在一个更易于管理的方式来封装逻辑。也同意 - 你为什么要通过5个参数，如果你需要的只有三，通过这个例子来看？
您可以使用此查询呢？

 从选择TrustAccountValue
（
 SELECT MAX（tal.trustaccountlogid），tal.TrustAccountValue
 从TrustAccountLog AS TAL
 INNER JOIN TrustAccount的助教ta.TrustAccountID = tal.TrustAccountID
 INNER JOIN用户USR ON usr.UserID = ta.UserID
 WHERE usr.UserID = 70402 AND
 ta.TrustAccountID = 117249 AND
 tal.TrustAccountLogDate＆LT; 3/1/2010 12:00:00 AM
 通过tal.TrustAccountValue组
）问
 

而且，对于它的价值，你正在使用明确的日期格式，取决于执行查询的用户的语言设置。对于我为例，这是月3日，而不是3月1日。检查了这一点：

 设置语言，美国英语
走
选择@@语言--us_english
选择转换（日期时间，3/1/2010 12:00:00 AM'）
走
集英语言
走
选择@@语言--british
选择转换（日期时间，3/1/2010 12:00:00 AM'）
 

建议的方法是使用ISOyyyymmdd格式HH：MM：SS

 选择转换（日期时间，20100301 00:00:00）--midnight 00，中午12
 

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;

解决方案

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

这篇关于SQL查询在.NET应用程序缓慢，但在瞬时SQL Server Management Studio中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！