C# 使用 ADO.Net 的查询的连接超时异常

Connection Timeout exception for a query using ADO.Net

提问by dragon

Update: Looks like the query does not throw any timeout. The connection is timing out.

更新：看起来查询不会引发任何超时。连接超时。

This is a sample code for executing a query. Sometimes, while executing time consuming queries, it throws a timeout exception.

这是用于执行查询的示例代码。有时，在执行耗时的查询时，它会抛出超时异常。

I cannotuse any of these techniques: 1) Increase timeout. 2) Run it asynchronously with a callback. This needs to run in a synchronous manner.

我不能使用这些技术中的任何一种：1) 增加超时。2）使用回调异步运行它。这需要以同步方式运行。

please suggest any other techinques to keep the connection alive while executing a time consuming query?

请建议任何其他技术来在执行耗时的查询时保持连接活动？

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

采纳答案by Gulzar Nazim

Since you are using ExecuteNonQuery which does not return any rows, you can try this polling based approach. It executes the query in an asyc manner (without callback) but the application will wait (inside a while loop) until the query is complete. From MSDN. This should solve the timeout problem. Please try it out.

由于您使用的是不返回任何行的 ExecuteNonQuery，您可以尝试这种基于轮询的方法。它以异步方式（无回调）执行查询，但应用程序将等待（在 while 循环内）直到查询完成。来自MSDN。这应该可以解决超时问题。请尝试一下。

But, I agree with others that you should think more about optimizing the query to perform under 30 seconds.

但是，我同意其他人的看法，您应该更多地考虑优化查询以在 30 秒内执行。

        IAsyncResult result = command.BeginExecuteNonQuery();

        int count = 0;
        while (!result.IsCompleted)
        {
            Console.WriteLine("Waiting ({0})", count++);
            System.Threading.Thread.Sleep(1000);
        }
        Console.WriteLine("Command complete. Affected {0} rows.",
        command.EndExecuteNonQuery(result));

回答by core

command.CommandTimeout *= 2;

That will double the default time-out, which is 30 seconds.

这将使默认超时时间加倍，即 30 秒。

Or, put the value for CommandTimeout in a configuration file, so you can adjust it as needed without recompiling.

或者，将 CommandTimeout 的值放在配置文件中，这样您就可以根据需要调整它，而无需重新编译。

回答by Seibar

If you absolutely cannot increase the timeout, your only option is to reduce the time of the query to execute within the default 30 second timeout.

如果您绝对不能增加超时，您唯一的选择是在默认的 30 秒超时内减少执行查询的时间。

回答by Seibar

You should break your query up into multiple chunks that each execute within the timeout period.

您应该将查询分解为多个块，每个块都在超时期限内执行。

回答by Sam Erwin

I have to agree with Terrapin.

我必须同意Terrapin。

You have a few options on how to get your time down. First, if your company employs DBAs, I'd recommend asking them for suggestions.

关于如何减少时间，您有几种选择。首先，如果您的公司雇用 DBA，我建议您向他们征求建议。

If that's not an option, or if you want to try some other things first here are your three major options:

如果这不是一个选择，或者如果您想先尝试其他一些事情，这里是您的三个主要选择：

1. Break up the query into components that run under the timeout. This is probably the easiest.
2. Change the query to optimize the access path through the database (generally: hitting an index as closely as you can)
3. Change or add indices to affect your query's access path.

1. 将查询分解为在超时下运行的组件。这可能是最简单的。
2. 更改查询以优化通过数据库的访问路径（通常：尽可能接近索引）
3. 更改或添加索引以影响查询的访问路径。

回答by Pop Catalin

You should first check your query to see if it's optimized and it isn't somehow running on missing indexes. 30 seconds is allot for most queries, even on large databases if they are properly tuned. If you have solid proof using the query plan that the query can't be executed any faster than that, then you should increase the timeout, there's no other way to keep the connection, that's the purpose of the timeout to terminate the connection if the query doesn't complete in that time frame.

您应该首先检查您的查询以查看它是否已优化并且它没有以某种方式在丢失的索引上运行。30 秒是分配给大多数查询的，即使是在适当调整的大型数据库上。如果您使用查询计划有确凿的证据表明查询的执行速度不能比这更快，那么您应该增加超时时间，没有其他方法可以保持连接，这就是超时终止连接的目的，如果查询未在该时间范围内完成。

回答by Mitchel Sellers

If you are constrained from using the default process of changing the timeout value you will most likely have to do a lot more work. The following options come to mind

如果您无法使用更改超时值的默认过程，您很可能需要做更多的工作。想到了以下选项

1. Validate with your DBA's and another code review that you have truly optimized the query as best as possible
2. Work on the underlying DB structure to see if there is any gain you can get on the DB side, creating/modifying an idex(es).
3. Divide it into multiple parts, even if this means running procedures with multiple return parameters that simply call another param. (This option is not elegant, and honestly if your code REALLY is going to take this much time I would be going to management and re-discussing the 30 second timeout)

1. 与您的 DBA 和另一次代码审查一起验证您是否真正优化了查询
2. 在底层 DB 结构上工作，看看您是否可以在 DB 端获得任何收益，创建/修改 idex(es)。
3. 将它分成多个部分，即使这意味着运行具有多个返回参数的过程，这些返回参数只是调用另一个参数。（这个选项并不优雅，老实说，如果你的代码真的要花这么多时间，我会去管理并重新讨论 30 秒超时）

回答by Geir-Tore Lindsve

We recently had a similar issue on a SQL Server 2000 database.

我们最近在 SQL Server 2000 数据库上遇到了类似的问题。

During your query, run this query on your master database on the db server and see if there are any locks you should troubleshoot:

在查询期间，在 db 服务器上的 master 数据库上运行此查询，看看是否有任何应该排除故障的锁：

select 
  spid,
  db_name(sp.dbid) as DBname,
  blocked as BlockedBy,
  waittime as WaitInMs,
  lastwaittype,
  waitresource,
  cpu,
  physical_io,
  memusage,
  loginame,
  login_time,
  last_batch,
  hostname,
  sql_handle
from sysprocesses sp
where (waittype > 0 and spid > 49) or spid in (select blocked from sysprocesses where blocked > 0)

SQL Server Management Studio 2008 also contains a very cool activity monitor which lets you see the health of your database during your query.

SQL Server Management Studio 2008 还包含一个非常酷的活动监视器，可让您在查询期间查看数据库的健康状况。

In our case, it was a networkio lock which kept the database busy. It was some legacy VB code which didn't disconnect its result set quick enough.

在我们的例子中，它是一个 networkio 锁，它使数据库保持忙碌。这是一些遗留的 VB 代码，它没有足够快地断开其结果集。

回答by Geir-Tore Lindsve

I tend to dislike increasing the connection/command timeout since in my mind that would be a matter of taking care of the symptom, not the problem

我倾向于不喜欢增加连接/命令超时，因为在我看来这将是处理症状的问题，而不是问题

回答by MatthewMartin

If you are prohibited from using the features of the data access API to allow a query to last more than 30 seconds, then we need to see the SQL.

如果您被禁止使用数据访问 API 的功能来允许查询持续 30 秒以上，那么我们需要查看 SQL。

The performance gains to be made by optimizing the use of ADO.NET are slight in comparison to the gains of optimizing the SQL.

与优化 SQL 的收益相比，通过优化 ADO.NET 的使用获得的性能收益是微不足道的。

And you already are using the most efficient method of executing SQL. Other techniques would be mind numbingly slower (although, if you did a quick retrieval of your rows and some really slow client side processing using DataSets, you might be able to get the initial retrieval down to less than 30 seconds, but I doubt it.)

而且您已经在使用执行 SQL 的最有效方法。其他技术会慢得让人麻木（尽管，如果您对行进行了快速检索，并且使用 DataSet 进行了一些非常缓慢的客户端处理，您可能能够将初始检索时间缩短到 30 秒以内，但我对此表示怀疑。 )

If we knew if you were doing inserts, then maybe you should be using bulk insert. But we don't know the content of your sql.

如果我们知道您是否在进行插入，那么也许您应该使用批量插入。但是我们不知道你的sql的内容。