为什么有些sql查询与SqlCommand一起使用时要慢得多?

I have a stored procedure that executes much faster from Sql Server Management Studio (2 seconds) than when run with System.Data.SqlClient.SqlCommand (times out after 2 minutes).

我有一个存储过程，它在Sql Server Management Studio中执行的速度比在System.Data.SqlClient中运行时快得多(2秒)。SqlCommand(2分钟后超时)。

What could be the reason for this?

原因何在?

Details: In Sql Server Management Studio this runs in 2 seconds (on production database):

详细信息:在Sql Server Management Studio中，运行时间为2秒(在生产数据库上):

EXEC sp_Stat
    @DepartmentID = NULL

In .NET/C# the following times out after 2 minutes (on production database):

在. 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())
            {
            }
        }
    }
}

I also tried with selectCommand = "sp_Stat", CommandType = StoredProcedure, and an SqlParameter, but it's the same result.

我还尝试了selectCommand = "sp_Stat"， CommandType = StoredProcedure，以及一个SqlParameter，但结果是相同的。

And without EXEC it's the same result as well.

没有执行，结果也是一样的。

On an almost data-empty development database both cases finishes in less than 1 second. So it's related to that there's a lot of data in the database, but it seems to only happen from .NET...

在几乎为数据空的开发数据库中，这两种情况都在不到1秒内完成。所以这与数据库中有很多数据有关，但这似乎只发生在。net中…

What Marc Gravell wrote about different SET values makes the difference in the presented case.

Marc Gravell关于不同集合值所写的内容在本例中是不同的。

SQL Server Profiler showed that Sql Server Management Studio runs the following SET's that .NET Sql Client Data Provider does not:

SQL Server Profiler显示SQL Server Management Studio运行。net SQL客户端数据提供程序不运行的集合:


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

When I included these, the same query took the same amount of time in SSMS and .NET. And the responsible SET is ...

当我包含这些时，相同的查询在SSMS和。net中花费了相同的时间。负责任的一组是……

SET ARITHABORT ON

What have I learnt? Maybe to use a profiler instead of guessing...

我学到了什么?也许可以使用剖析器而不是猜测……

(The solution at first seemed to be related to parameter sniffing. But I had mixed some things up...)

(最初的解决方案似乎与参数嗅探有关。但我把一些事情搞混了……

4 个解决方案

#1

Another thing that can be important is the SET options that are enabled. Some of these options change the query plan sufficiently to change the profile. Some can have a huge impact if you are looking at (for example) a calculated + persisted (and possibly indexed) column: if the SET options aren't compatible, it can be forced to re-calculate the values, rather than using the indexed value - which can change an index seek into a table scan + calculation.

另一件重要的事情是启用的SET选项。其中一些选项充分地更改查询计划以更改概要文件。一些可以产生巨大的影响,如果你看(例如)计算+坚持(并可能索引)列:如果设置选项不兼容,它可以*计算值,而不是使用索引值——寻找到一个可以改变索引表扫描+计算。

Try using the profiler to see what SET options are "in play", and see if using those options changes things.

尝试使用profiler来查看哪些设置选项是“在运行”的，并查看使用这些选项是否会改变事情。

Another impact is the connection string; for example, if you enable MARS that can change the behaviour in subtle ways.

另一个影响是连接字符串;例如，如果你能让火星以微妙的方式改变它的行为。

Finally, transactions (implicit (TransactionScope) or explicit) can have a huge impact, depending on the isolation level.

最后，根据隔离级别，事务(隐式(TransactionScope)或显式)可以产生巨大的影响。

#2

This is almost certainly due to an 'incorrect' cached query plan. This has come up on SO quite a few times.

这几乎肯定是由于“不正确”的缓存查询计划。这种情况已经发生了好几次了。

Do you have up-to-date statistics? A regular scheduled index maintenance plan?

你有最新的统计数字吗?定期的索引维护计划?

You can test if it is definitely due to the cached query plan by adding this to your stored procedure definition:

您可以通过将它添加到存储过程定义中来测试它是否是缓存的查询计划:

CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE...

This will re-index an entire database (caution if database is very large!):

这将重新索引整个数据库(如果数据库很大，请注意):

exec sp_msforeachtable "dbcc dbreindex('?')"

SO posts:

所以文章:

Big difference in execution time of stored proc between Managment Studio and TableAdapter.

管理工作室和TableAdapter存储过程执行时间的差异较大。

Parameter Sniffing (or Spoofing) in SQL Server

参数嗅探(或欺骗)在SQL Server中。

optimize for unknown for SQL Server 2005?

为SQL Server 2005优化未知?

Different Execution Plan for the same Stored Procedure

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

#3

Had a similar issue and it turns out having MultipleActiveResultSets=true in the connection string (which is supposed to have minimal impact) was making pulling 1.5mil records over a remote connection take 25 minutes instead of around 2 minutes.

有一个类似的问题，结果是在连接字符串中有多路复用(应该是最小的影响)，在远程连接上拉出1.5mil记录需要25分钟，而不是2分钟。

#4

We had a similiar issue, where a query would complete in 2 seconds in SSMS and take more than 90 seconds when called from a .NET client (we wrote several VB/C# apps/sites to test it.)

我们有一个相似的问题，在SSMS中一个查询将在2秒内完成，当从。net客户端调用时将花费超过90秒(我们编写了几个VB/ c#应用程序/站点来测试它)。

We suspected that the query plan would be different, and rewrote the query with explicit looping ("inner loop join" and "with index") hints. This solved the problem.

我们怀疑查询计划是不同的，并使用显式循环(“内部循环联接”和“带有索引”)提示重新编写查询。这解决了这个问题。

#1