这是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; 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;
如果这是参数嗅探,请尝试添加option(recompile)到查询的末尾。我建议创建一个存储过程,以更易于管理的方式封装逻辑。也同意- 根据示例判断,如果只需要三个参数,为什么要传递五个参数呢?您可以改用此查询吗?
option(recompile)
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
而且,根据执行查询的用户的语言设置,使用的日期格式不明确,这是值得的。例如,对我来说,这是1月3日,而不是3月1日。看一下这个:
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')
推荐的方法是使用“ ISO”格式yyyymmdd hh:mm:ss
select convert(datetime, '20100301 00:00:00') --midnight 00, noon 12