code花费时间变化系统与系统 [英] Code takes time that varies system to system

问题描述

我面临的一个问题，去年15天。我有一个code，其中包含一个do-whil​​e循环和内有for循环4。在每个循环以下函数被调用。

I am facing a problem for last 15 days. I have a code which contains a do-while loop and inside that there are 4 for loops. in each loop the following function is called.

Public Function retds1(ByVal SPName As String, ByVal conn As SqlConnection, Optional ByVal ParameterValues() As Object = Nothing) As DataSet
    dconn = New SqlConnection(ConfigurationManager.ConnectionStrings("webriskpro").ConnectionString)
    Try
        sqlcmd = New SqlCommand
        ds = New DataSet
        If dconn.State = ConnectionState.Open Then dconn.Close()
        sqlcmd = New SqlCommand(SPName, dconn)
        sqlcmd.CommandType = CommandType.StoredProcedure
        dconn.Open()
        SqlCommandBuilder.DeriveParameters(sqlcmd)
        If Not ParameterValues Is Nothing Then
            For i As Integer = 1 To ParameterValues.Length
                sqlcmd.Parameters(i).Value = ParameterValues(i - 1)
            Next
        End If
        da = New SqlDataAdapter(sqlcmd)
        da.Fill(ds)
    Catch ex As Exception
        send_prj_err2mail(ex, SPName, "")
    Finally
        dconn.Close()
    End Try
    Return ds
End Function

现在的问题是
1）在我的本地系统，我得到超时。之前完成上述操作超时时间已过或服务器没有响应error.So我改变了功能，如下。（即）我加的CommandTimeout = 0

Now the issues are 1) in my local system i got "timeout expired. the timeout period elapsed prior to completion of the operation or the server is not responding" error.So i changed the function as the following.(i.e) i added CommandTimeout=0

Public Function retds1(ByVal SPName As String, ByVal conn As SqlConnection, Optional ByVal ParameterValues() As Object = Nothing) As DataSet
    dconn = New SqlConnection(ConfigurationManager.ConnectionStrings("webriskpro").ConnectionString)
    Try
        sqlcmd = New SqlCommand
        ds = New DataSet
        If dconn.State = ConnectionState.Open Then dconn.Close()
        sqlcmd = New SqlCommand(SPName, dconn)
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandTimeout = 0
        dconn.Open()
lp:
        SqlCommandBuilder.DeriveParameters(sqlcmd)
        If Not ParameterValues Is Nothing Then
            For i As Integer = 1 To ParameterValues.Length
                sqlcmd.Parameters(i).Value = ParameterValues(i - 1)
            Next
        End If
        da = New SqlDataAdapter(sqlcmd)
        da.SelectCommand.CommandTimeout = 0
        da.Fill(ds)
    Catch ex As Exception
        If ex.Message.ToString.Contains("Timeout expired") Then
            GoTo lp
        End If
        send_prj_err2mail(ex, SPName, "")
    Finally
        dconn.Close()
    End Try
    Return ds
End Function

2）但是，什么是发生在超时异常依然在下。通过捕获它会解决这个问题。但整个过程走1小时。

2) But what happen is the "Timeout expired" exception is still coming. by catching it will resolve the problem. But the whole process is taking 1 hour.

3）同样的问题在服务器计算机了。所以我们改变了服务器。现在在备份服务器的整个code仅需3分钟。但在我的本地机器和主服务器在同一code时间超过30分钟。

3) The same problem is in server machine too. So we changed the server. now In the backup server the whole code takes only 3 minutes. but the same code in my local machine and main server takes more than 30 minutes.

补充：

连接字符串

<add name="webriskpro" connectionString="Data Source=TECH01\SQL2005;Initial Catalog=webriskpro1;User ID=sa;Password=#basix123; pooling=false;connection timeout=600;"/>

我有2个问题。

1. 为什么会出现超时已过期。只在我的本地系统和主服务器的机器，但不是在备份服务器计算机？

1. Why does the "timeout expired .." come only on my local system and main server machine but not in the backup server machine?

由备份服务器所花费的时间只有3分钟，code。但在我的本地系统和主服务器，它是大约30分钟等等。（所有人都具有相同的来源，数据库）。

The time taken by the backup server is only 3 minutes for the code. but in my local system and main server , it is about 30 minutes and more.(all are having same source, database).

更新：

我已经检查在SQL事件探查器的过程。由于循环有没有限制的，我们不知道有多少次迭代。前几迭代，持续时间低于120则某处同一SP，持续时间为13000，1200和这样。是什么原因导致这个吗？

I have checked the process in sql Profiler. Since the loop has no limit, we don't know how many times it iterates. for the first few iterations , Duration is below 120. then somewhere for the same SP, duration is 13000, 1200 and like that. what causes this one?

在SQL事件探查器将显示以下，即使我在存储过程中设置ARITHABORT

The following is shown in the sql Profiler, even i set Arithabort on in my stored procedure

-- network protocol: Named Pipes
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

任何人都可以建议我会是什么原因呢？

Can anyone suggest me what will be the cause?

推荐答案

我不认为你应该使用的CommandTimeout = 0。这将会使你的脚本无限期地等待连接成功，即使这永远不会发生。

I dont think you should use CommandTimeout=0. This will make your script wait indefinitely for a successful connection, even if that's never going to happen.

我建议建立一个重试循环，因为你已经在你的第二个解决方案一样。只有保持在的CommandTimeout一个合理的值。

I'd advise to build in a retry loop, as you already did in your second solution. Only keep the CommandTimeout at a reasonable value.

此外，您应该调查为什么你的连接失败。也许你的服务器已经达到了其SQL连接的最大数目？也许你可以找到你的SQL服务器日志的额外有用的信息。

Also, you should investigate why your connection is failing. Maybe your server has reached its maximum number of SQL connections? Maybe you can find extra useful information in your SQL server logs.

这篇关于code花费时间变化系统与系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！