为什么与 SqlCommand 一起使用时某些 sql 查询会慢很多？

Question

我有一个存储过程，从 Sql Server Management Studio 执行（2 秒）比使用 System.Data.SqlClient.SqlCommand 运行（2 分钟后超时）要快得多。

这可能是什么原因？

---

细节： 在 Sql Server Management Studio 中，这将在 2 秒内运行（在生产数据库上）：

EXEC sp_Stat
    @DepartmentID = NULL

在 .NET/C# 中，2 分钟后会超时（在生产数据库上）：

string selectCommand = @"
EXEC sp_Stat
    @DepartmentID = NULL";
string connectionString = "server=***;database=***;user id=***;pwd=***";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(selectCommand, connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
            }
        }
    }
}

我还尝试了 selectCommand = "sp_Stat" 、CommandType = StoredProcedure 和 SqlParameter，但结果相同。

如果没有 EXEC ，结果也是一样的。

在几乎数据为空的开发数据库上，这两种情况都在不到 1 秒的时间内完成。 因此，这与数据库中有大量数据有关，但它似乎只发生在 .NET 中......

---

Marc Gravell 所写的关于不同 SET 值的内容在所呈现的情况下产生了差异。

SQL Server Profiler 显示 Sql Server Management Studio 运行以下 SET，而 .NET Sql Client Data Provider 则不会：

SET ROWCOUNT 0 
SET TEXTSIZE 2147483647 
SET NOCOUNT OFF 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ARITHABORT ON 
SET LOCK_TIMEOUT -1 
SET QUERY_GOVERNOR_COST_LIMIT 0 
SET DEADLOCK_PRIORITY NORMAL 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
SET ANSI_NULLS ON 
SET ANSI_NULL_DFLT_ON ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET CURSOR_CLOSE_ON_COMMIT OFF 
SET IMPLICIT_TRANSACTIONS OFF 
SET QUOTED_IDENTIFIER ON
SET NOEXEC, PARSEONLY, FMTONLY OFF

当我包含这些时，相同的查询在 SSMS 和 .NET 中花费相同的时间。网。 而负责的SET是......

SET ARITHABORT ON

我学到了什么？ 也许使用探查器而不是猜测......

（解决方案起初似乎与参数嗅探有关。但我混淆了一些东西......）

Answer 1

另一件重要的事情是SET选项 已启用。 其中一些选项足以更改查询计划以更改配置文件。 如果您正在查看（例如）计算+持久（并且可能是索引）列，有些可能会产生巨大影响：如果 SET 选项不兼容，则可能会被迫重新计算值，而不是使用索引值 - 这可以将索引查找更改为表扫描+计算。

尝试使用探查器查看哪些 SET 选项正在“发挥作用”，并查看使用这些选项是否会改变情况。

另一个影响是连接字符串； 例如，如果启用 MARS，可以以微妙的方式改变行为。

最后，事务（隐式 (TransactionScope) 或显式）可能会产生巨大影响，具体取决于隔离级别。

Answer 2

这几乎肯定是由于“不正确”的缓存查询计划造成的。 这个问题已经出现过很多次了。

您有最新的统计数据吗？ 定期安排索引维护计划？

您可以通过将其添加到存储过程定义中来测试这是否肯定是由于缓存的查询计划造成的：

CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE...

这将重新索引整个数据库（如果数据库非常大，请小心！）：

exec sp_msforeachtable "dbcc dbreindex('?')"

SO posts：

Managment Studio 和 TableAdapter 之间存储过程的执行时间存在巨大差异.

SQL Server 中的参数嗅探（或欺骗）

针对 SQL Server 2005 的未知进行优化？

同一存储过程的不同执行计划

Answer 3

遇到了类似的问题，结果发现连接字符串中的 MultipleActiveResultSets=true （应该影响最小）导致通过远程连接提取 150 万条记录需要 25 分钟，而不是大约 2 分钟。

Answer 4

我们遇到了一个类似的问题，在 SSMS 中查询将在 2 秒内完成，而从 .NET 客户端调用时则需要 90 秒以上（我们编写了几个 VB/C# 应用程序/站点来测试它。）

我们怀疑查询计划会有所不同，并使用显式循环（“内循环连接”和“带索引”）提示重写查询。 这解决了问题。