使用ADO.Net查询时出现连接超时异常

9
更新: 看起来查询没有抛出任何超时异常。连接正在超时。 这是一个执行查询的示例代码。有时,在执行耗时的查询时,会抛出超时异常。 我不能使用以下任何技术: 1)增加超时时间。 2)使用回调异步运行。这需要同步运行。 请建议任何其他技术,在执行耗时的查询时保持连接活动状态?
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();
    }
}

抱歉,我需要一些澄清:为什么您不能增加超时时间? - C Hogg
这违反了公司的标准。:( 我尽可能地优化了查询,但仍需要一些时间。 - dragon
10
如果你不能增加超时时间,也不能减少执行时间,并且必须同步地完成任务,那么你就是在定义一个无法解决的问题。 - Colin Burnett
你的查询是否一直超时,还是偶尔会出现,即有时它可以工作,有时它不能。如果是偶发性的,我可以给你一些我们为死锁类似问题编写的代码。 - Rashmi Pandit
好的,您是指SqlConnection超时,还是承载CreateCommand调用的任何东西(例如ASP.NET、Console应用程序等...)?实际的异常错误会很有帮助。 - stephbu
显示剩余3条评论
16个回答

18

由于您正在使用不返回任何行的ExecuteNonQuery,您可以尝试这种基于轮询的方法。它以异步方式执行查询(没有回调),但应用程序将在等待(在while循环内)查询完成之前保持运行状态。参考MSDN。这应该解决超时问题,请尝试一下。

但是我同意其他人的想法,您应该考虑优化查询,使其在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));

谢谢,这似乎可行。如果有更好的方案出现,我会再等一段时间。 - dragon
检查结果的IsCompleted是多余的。 - M. Jahedbozorgan
OP表示查询超时了。无论是同步还是异步,查询超时问题都是一样的。例如,在您的查询中加入WAITFOR DELAY '00:01:00'。如果您的command.Timeout为30秒,则无论是异步调用还是同步调用,在30秒时仍会抛出查询超时异常。 - stephbu
@stephbu - 看起来查询没有超时,而是连接超时了。当前这个解决方案还可以用。 - dragon
2
我不是100%确定,但我认为SqlConnection不能超时 - 据我所知,只有打开新连接可能会超时(默认为15秒)。因此,我仍然相信命令超时了(如果命令在尝试从连接中读取时超时,这可能看起来像连接超时)。 - Daniel Brückner

4

您应该首先检查您的查询是否已经进行了优化,并且没有在缺失索引的情况下运行。即使对于大型数据库,如果它们被正确地调整,则30秒对于大多数查询来说是很长的时间。如果您有明确的证据表明查询计划不能比此更快地执行查询,那么您应该增加超时时间,因为这是保持连接的唯一方法,这就是超时的目的:如果查询不能在该时间范围内完成,则终止连接。


1

我必须同意Terrapin的观点。

您有几个选项可以缩短时间。首先,如果您的公司雇用了数据库管理员(DBAs),我建议向他们寻求建议。

如果这不是一个选择,或者您想先尝试其他方法,那么您有三个主要选项:

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

1

如果您受限于使用更改超时值的默认过程,那么您很可能需要做更多的工作。以下选项值得考虑:

  1. 与您的数据库管理员进行验证,并进行另一个代码审查,以确保您已经尽可能地优化了查询。
  2. 在底层数据库结构上进行工作,看看是否可以从数据库端获得任何收益,创建/修改索引。
  3. 将其分成多个部分,即使这意味着运行具有多个返回参数的过程,只需调用另一个参数即可。(这个选项并不优雅,而且老实说,如果您的代码真的要花费这么长时间,我会去找管理层重新讨论30秒超时的问题)

真的。问题是管理层希望我探索所有选项。 :) - dragon

1

如果您被禁止使用数据访问 API 的功能,以允许查询持续时间超过 30 秒,则我们需要查看 SQL。

通过优化 ADO.NET 的使用来获得的性能提升相对较小,与优化 SQL 相比要小得多。

而且,您已经在使用执行 SQL 最有效的方法。其他技术将非常缓慢(虽然,如果您快速检索行并使用 DataSet 进行一些非常缓慢的客户端处理,可能能够将初始检索减少到不到 30 秒,但我怀疑这个方法)。

如果我们知道您正在进行插入操作,那么也许您应该使用批量插入。但是我们不知道您 SQL 的内容。


1

这是一个丑陋的黑科技,但可能会帮助您暂时解决问题,直到您能够修复真正的问题

    private static void CreateCommand(string queryString,string connectionString)
    {
        int maxRetries = 3;
        int retries = 0;
        while(true)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
                break;
            }
            catch (SqlException se)
            {
                if (se.Message.IndexOf("Timeout", StringComparison.InvariantCultureIgnoreCase) == -1)
                    throw; //not a timeout

                if (retries >= maxRetries)
                    throw new Exception( String.Format("Timedout {0} Times", retries),se);

                //or break to throw no error

                retries++;
            }
        }
    }

1

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

在您的查询期间,在数据库服务器上运行此查询,查看是否有任何需要排除故障的锁定:

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 还包含一个非常酷的活动监视器,它可以让您在查询期间查看数据库的运行状况。

在我们的情况下,是 networkio 锁定了数据库。这是一些遗留的 VB 代码,没有足够快地断开其结果集。


0

也许值得尝试将结果进行分页返回。


0

你有没有考虑将查询分解成几个较小的块?

此外,你是否已经在以下位置的管理工具中运行了数据库引擎调整顾问:

Management Studio > 工具 > 数据库引擎调整顾问

最后,我们能否看一下查询本身?

谢谢


0

你尝试过将SQL语句放在存储过程中吗?它们似乎具有更好的内存管理。我以前在使用经典ADO的计划SQL语句中遇到过类似于这样的超时问题,其中包含了大量结果的内部查询。例如:select * from (select ....) t inner join somthingTable。

其他提示: 1. 使用with(nolock)执行提示进行读取,虽然不太建议,但速度会更快。 2. 查看要运行的SQL的执行计划,并减少行扫描和表连接的顺序。 3. 为表添加一些索引以加快读取速度。 4. 我还发现删除行非常昂贵,您可以尝试限制每次调用的行数。 5. 将@table变量与#temporary表进行交换,在过去也对我有用。 6. 您可能还保存了错误的执行计划(听说过,但从未见过)。

希望这可以帮助到您。


网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,