SQL查询中的SQL查询速度很慢,但在SQL Server Management Studio中是即时的

这是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有一个Users表,一个TrustAccount表和一个TrustAccountLog表.
用户:包含用户及其详细信息
TrustAccount:用户可以拥有多个TrustAccounts.
TrustAccountLog:包含对所有TrustAccount"移动"的审计.一个
TrustAccount与多个TrustAccountLog项关联.现在这个查询在SQL Server Management Studio中以毫秒为单位执行,但由于某些奇怪的原因,它有时会永远存在于我的C#应用​​程序中甚至超时(120秒).

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

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;

Daniel Rensh.. 64

根据我的经验,查询在SSMS中运行速度快但从.NET缓慢运行的常见原因是由于连接的SET不同.当SSMS打开连接时SqlConnection,SET会自动发出一堆命令来设置执行环境.不幸的是SSMS和默认值SqlConnection不同SET.

一个常见的区别是SET ARITHABORT.尝试SET ARITHABORT ON从.NET代码发出第一个命令.

SQL事件探查器可用于监视SETSSMS和.NET发出的命令,以便您可以找到其他差异.

以下代码演示了如何发出SET命令,但请注意此代码尚未经过测试.

using (SqlConnection conn = new SqlConnection("")) {
    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.
}

这对我来说是一个非常错误的信念,你可以阅读这篇[博客](http://www.sommarskog.se/query-plan-mysteries.html)进行深入解释.更改此参数主要导致查询使用其他查询计划缓存并绕过错误的缓存查询计划.这很可能无法解决实际问题,这很可能是索引问题. (6认同)

经过大量的搜索,你终于解决了我的问题.非常感谢! (4认同)

为此+1 - 设置的差异几乎肯定是罪魁祸首. (3认同)

哇,这奇怪地解决了我的问题......什么是ARITHABORT？ (3认同)

---

小智.. 28

如果这是参数嗅探,请尝试添加option(recompile)到查询的末尾.我建议创建一个存储过程以更易于管理的方式封装逻辑.同意 - 如果你只需要三个参数,为什么你会传递5个参数？你可以使用这个查询吗？

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

实际上ISO格式是yyyy-mm-ddThh24:mi:ss not yyyymmdd hh24:mi:ss (9认同)

---

Daz.. 11

在测试环境中遇到同样的问题,虽然实时系统(在同一个SQL服务器上)运行正常.添加OPTION(RECOMPILE)和OPTION(OPTIMIZE FOR(@ p1 UNKNOWN))没有帮助.

我使用SQL事件探查器来捕获.net客户端发送的确切查询,并发现它被包装exec sp_executesql N'select ...并且参数已被声明为nvarchars - 被比较的列是简单的varchars.

将捕获的查询文本放入SSMS确认它的运行速度与.net客户端的运行速度一样慢.

我发现将参数类型更改为AnsiText可以解决问题:

p = cm.CreateParameter() p.ParameterName = "@company" p.Value = company p.DbType = DbType.AnsiString cm.Parameters.Add(p)

我无法解释为什么测试和现场环境在性能上有如此显着的差异.

1> Daniel Rensh..：

---

根据我的经验,查询在SSMS中运行速度快但从.NET缓慢运行的常见原因是由于连接的SET不同.当SSMS打开连接时SqlConnection,SET会自动发出一堆命令来设置执行环境.不幸的是SSMS和默认值SqlConnection不同SET.

一个常见的区别是SET ARITHABORT.尝试SET ARITHABORT ON从.NET代码发出第一个命令.

SQL事件探查器可用于监视SETSSMS和.NET发出的命令,以便您可以找到其他差异.

以下代码演示了如何发出SET命令,但请注意此代码尚未经过测试.

using (SqlConnection conn = new SqlConnection("")) {
    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.
}

---

这对我来说是一个非常错误的信念,你可以阅读这篇[博客](http://www.sommarskog.se/query-plan-mysteries.html)进行深入解释.更改此参数主要导致查询使用其他查询计划缓存并绕过错误的缓存查询计划.这很可能无法解决实际问题,这很可能是索引问题.

---

经过大量的搜索,你终于解决了我的问题.非常感谢!

---

为此+1 - 设置的差异几乎肯定是罪魁祸首.

---

哇,这奇怪地解决了我的问题......什么是ARITHABORT？

2> 小智..：

---

如果这是参数嗅探,请尝试添加option(recompile)到查询的末尾.我建议创建一个存储过程以更易于管理的方式封装逻辑.同意 - 如果你只需要三个参数,为什么你会传递5个参数？你可以使用这个查询吗？

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

---

实际上ISO格式是yyyy-mm-ddThh24:mi:ss not yyyymmdd hh24:mi:ss

3> Daz..：

---

在测试环境中遇到同样的问题,虽然实时系统(在同一个SQL服务器上)运行正常.添加OPTION(RECOMPILE)和OPTION(OPTIMIZE FOR(@ p1 UNKNOWN))没有帮助.

我使用SQL事件探查器来捕获.net客户端发送的确切查询,并发现它被包装exec sp_executesql N'select ...并且参数已被声明为nvarchars - 被比较的列是简单的varchars.

将捕获的查询文本放入SSMS确认它的运行速度与.net客户端的运行速度一样慢.

我发现将参数类型更改为AnsiText可以解决问题:

p = cm.CreateParameter() p.ParameterName = "@company" p.Value = company p.DbType = DbType.AnsiString cm.Parameters.Add(p)

我无法解释为什么测试和现场环境在性能上有如此显着的差异.

4> erikkallen..：

---

最有可能的问题在于标准

tal.TrustAccountLogDate < @TrustAccountLogDate2

最佳执行计划将高度依赖于参数的值,传递1910-01-01(不返回任何行)肯定会导致不同于2100-12-31(返回所有行)的计划.

在查询中将值指定为文字时,SQL Server会知道在计划生成期间使用哪个值.使用参数时,SQL Server将仅生成一次计划然后重复使用它,如果后续执行中的值与原始值的差异太大,则计划将不是最佳的.

要解决此问题,您可以OPTION(RECOMPILE)在查询中指定.除非您创建过程WITH RECOMPILE,否则将查询添加到存储过程将无法帮助您解决此特定问题.

其他人已经提到了这个("参数嗅探"),但我认为这个概念的简单解释不会受到伤害.

5> LCJ..：

---

希望您的具体问题现在已经解决,因为它是一个旧帖子.

以下SET选项有可能影响计划重用(最后的完整列表)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ARITHABORT ON
GO

以下两个语句来自msdn - SET ARITHABORT

将ARITHABORT设置为OFF会对查询优化产生负面影响,从而导致性能问题.

SQL Server Management Studio的默认ARITHABORT设置为ON.将ARITHABORT设置为OFF的客户端应用程序可以接收不同的查询计划,从而难以对执行不佳的查询进行故障排除.也就是说,相同的查询可以在管理工作室中快速执行,但在应用程序中速度较慢.

另一个值得理解的有趣话题是应用程序Parameter Sniffing中的慢速,SSMS中的快速概述？理解性能之谜 - 来自Erland Sommarskog

另一种可能性是将VARCHAR列转换(内部)到NVARCHAR,同时使用Unicode输入参数,如在varchar列上排除SQL索引性能故障中所述- 作者:Jimmy Bogard

优化未知

在SQL Server 2008及更高版本中,请考虑OPTIMIZE FOR UNKNOWN.UNKNOWN:指定查询优化器使用统计数据而不是初始值来确定查询优化期间局部变量的值.

选项(重建)

如果重新编译是唯一的解决方案,请使用"OPTION(RECOMPILE)"而不是"WITH RECOMPILE".它有助于参数嵌入优化.阅读参数嗅探,嵌入和RECOMPILE选项 - 作者Paul White

设置选项

以下SET选项可能会影响计划重用,基于msdn - SQL Server 2008中的计划缓存

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