title: Code takes time that varies system to system
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Added:</p>
  
  <p>Connection string </p>
  
  <pre><code>&lt;add name="webriskpro" connectionString="Data Source=TECH01\SQL2005;Initial Catalog=webriskpro1;User ID=sa;Password=#basix123; pooling=false;connection timeout=600;"/&gt;
  </code></pre>
  
  <p>I have 2 questions.</p>
  
  <ol>
  <li><p>Why does the "timeout expired .." come only on my local system and main server machine but not in the backup server machine?</p></li>
  <li><p>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).</p></li>
  </ol>
  
  <p>Update:</p>
  
  <p>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?</p>
  
  <p>The following is shown in the sql Profiler, even i set Arithabort on in my stored procedure</p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>Can anyone suggest me what will be the cause?</p>