title: Execution Time Slower with each Iteration of the same SPROC
body: <p>Running the <strong>same</strong> Stored Procedure from C# .Net application <strong>over a network</strong> gets progressively slower with each subsequent execution. It appears to take <strong>twice</strong> the amount of time as the previous execution (up to a max value; read on). The execution time becomes progressively slower until 1 of 2 scenarios happens, at which point the first execution of the SPROC is "fast" again.</p>
  
  <ol>
  <li>If an <code>SqlConnection</code> is opened and remains open during all testing,
  the SPROC gets progressively slower until <strong>any other</strong> SPROC <strong>or query</strong> is run.</li>
  <li>If an <code>SqlConnection</code> is opened and closed around each execution, the
  SPROC gets progressively slower until <strong>at least 8 minutes</strong> has passed.</li>
  </ol>
  
  <p>This only happens with a few Stored Procedures. One is a simple <code>SELECT</code> query with 2 <code>JOINs</code>, (<em>SPROC 1</em>) another is a massive 1600+ line SPROC (<em>SPROC 2</em>).</p>
  
  <p>The execution times appear to never go beyond exactly 60 seconds for <em>SPROC 1</em> and 67 seconds for <em>SPROC 2</em>. <em>SPROC 1</em> takes less than a second to execute initially, and <em>SPROC 2</em> takes 7 seconds initially.</p>
  
  <p>This also only happens if the SPROC is run using the same <code>SqlConnection</code> in the application. As soon as 2 separate <code>SqlConnection</code> objects are used, they behave the same as stated above, but are independent. Running the SPROC multiple times on <code>SqlConnection1</code> gets progressively slower, but the first time the same SPROC is run on <code>SqlConnection2</code>, it's "fast". It will then also get slower when run multiple times on <code>SqlConnection2</code>.</p>
  
  <p>This does not happen if the application is run on the same computer with SQL Server 2008 R2 installed (running Windows Server 2008). The execution time is always consistent.</p>
  
  <p>Running the Stored Procedure from within Management Studio also does not get slower with each execution; it is always consistent.</p>
  
  <p>Clearing the execution plan cache (in SQL Server) has no effect on the observed behavior.</p>
  
  <p>It has taken quite a few days to narrow down this issue originally observed in a much larger application, in order to create a test app to easily test/reproduce it.</p>
  
  <p>From what I've read <a href="http://www.sqlmag.com/article/activex/swimming-in-the-net-connection-pool" rel="nofollow noreferrer">here</a>, there is a timeout of between 4 and 8 minutes (after <code>SqlConnection.Close()</code> is called in code) at which point it closes the database connection to the data source. This appears to be in line with the scenario 2 I mentioned above.</p>
  
  <p>This leads me to believe it is related to the <code>SqlConnection</code> used (and the underlying database connection to the data source) since connection pooling is enabled in my case, but <strong>why am I observing this behavior, and how do I fix it?</strong></p>
  
  <p>We are using the .Net 2.0 Framework, if that makes any difference.</p>
  
  <p>There are many fine details listed above, so please let me know if I need to clarify anything.</p>
  
  <p>The only Stack Overflow question with any similarities is <a href="https://stackoverflow.com/questions/6798039/sql-c-sharp-query-gets-slower-with-each-iteration">this</a>, but was unrelated to my issue.</p>
  
  <p><strong>Edit:</strong>
  The following code is executed in my WinForms test app on startup:</p>
  
  <pre><code>SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
  
  connectionStringBuilder.DataSource = m_DataSource;
  connectionStringBuilder.InitialCatalog = m_InitialCatalog;
  connectionStringBuilder.UserID = m_UserID;
  connectionStringBuilder.Password = m_Password;
  connectionStringBuilder.IntegratedSecurity = false;
  connectionString = connectionStringBuilder.ConnectionString;
  
  m_DatabaseConnection = new SqlConnection(connectionString);
  </code></pre>
  
  <p>I then have 2 buttons; one of which calls <em>SPROC 1</em> mentioned above, and the other calls a different SPROC which does not have the same slowdown issue. The following code is executed on either button click (only difference being the SPROC name):</p>
  
  <pre><code>m_DatabaseConnection.Open();
  m_DatabaseCommand = new SqlCommand("GetCompanies", m_DatabaseConnection);
  m_DatabaseCommand.Parameters.AddWithValue("@StatusID", StatusID);
  m_DatabaseCommand.CommandType = CommandType.StoredProcedure;
  m_DatabaseCommand.CommandTimeout = 0;
  
  SqlDataAdapter databaseDataAdapter = new SqlDataAdapter(m_DatabaseCommand);
  DataSet databaseDataSet = new DataSet();
  databaseDataAdapter.Fill(databaseDataSet);
  m_DatabaseConnection.Close();
  </code></pre>