asp.net - Code takes time that varies system to system

Question

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

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) 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) 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.

Added:

Connection string

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

I have 2 questions.

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

2. 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).

Update:

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?

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?

Answer 1

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.

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.

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.