title: Why does a stored procedure executed via ADO.NET take many times longer than SQL Server Management Studio?
body: <p>I have a stored procedure that is being executed from a legacy ASP.NET WebForms application that is timing out. The <code>CommandTimeout</code> property has been set to 6 minutes. The stored procedure queries a table with 15 million rows in it and joins to other tables.... so it is slow. However, it only returns 20 rows, so network performance is not the issue here.</p>
  
  <pre><code>var data = new DataSet();
  
  using (var connection = GetConnection())
  using(var command = connection.CreateCommand())
  using(var dataAdapter = new SqlDataAdapter(command))
  {
     command.CommandTimeout = 360;
     command.CommandType = CommandType.StoredProcedure;
     command.CommandText = "praGetTradesForProductIdReassignment";
     command.Parameters.AddWithValue("@int4_TradeId", (object)tradeId ?? DBNull.Value);
     command.Parameters.AddWithValue("@int4_FeedId", (object)feedId ?? DBNull.Value);
     command.Parameters.AddWithValue("@int4_SystemId", (object)systemId ?? DBNull.Value);
     command.Parameters.AddWithValue("@int4_TradeCode", (object)tradeCode ?? DBNull.Value);
     command.Parameters.AddWithValue("@int4_ProductID", (object)productId ?? DBNull.Value);
     command.Parameters.AddWithValue("@sortColumn", sortColumn);
     command.Parameters.AddWithValue("@sortDirection", sortDirection);
     command.Parameters.AddWithValue("@pageIndex", pageIndex);
     command.Parameters.AddWithValue("@pageSize", pageSize);
  
     dataAdapter.Fill(data);                
  }
  
  return data;
  </code></pre>
  
  <p>When I execute the same stored procedure, with the same parameters from within SQL Server Management Studio, it completes successfully after about 50 seconds.</p>
  
  <p>I've run a SQL Trace using Profiler which shows that the ADO.NET execution is taking way longer than the SQL Server Management Studio one (50 vs 356 seconds).</p>
  
  <pre><code>  &lt;Events&gt;
      &lt;Event id="13" name="SQL:BatchStarting"&gt;
        &lt;Column id="1" name="TextData"&gt;exec praGetTradesForProductIdReassignment @int4_TradeId=NULL,@int4_FeedId=NULL,@int4_SystemId=NULL,@int4_TradeCode=NULL,@int4_ProductID=NULL,@sortColumn=N'',@sortDirection=0,@pageIndex=0,@pageSize=20&lt;/Column&gt;
        &lt;Column id="10" name="ApplicationName"&gt;Microsoft SQL Server Management Studio - Query&lt;/Column&gt;
      &lt;/Event&gt;
      &lt;Event id="12" name="SQL:BatchCompleted"&gt;
        &lt;Column id="10" name="ApplicationName"&gt;Microsoft SQL Server Management Studio - Query&lt;/Column&gt;
        &lt;Column id="1" name="TextData"&gt;exec praGetTradesForProductIdReassignment @int4_TradeId=NULL,@int4_FeedId=NULL,@int4_SystemId=NULL,@int4_TradeCode=NULL,@int4_ProductID=NULL,@sortColumn=N'',@sortDirection=0,@pageIndex=0,@pageSize=20&lt;/Column&gt;
        &lt;Column id="13" name="Duration"&gt;49946289&lt;/Column&gt;
      &lt;/Event&gt;
      &lt;Event id="10" name="RPC:Completed"&gt;
        &lt;Column id="10" name="ApplicationName"&gt;.Net SqlClient Data Provider&lt;/Column&gt;
        &lt;Column id="1" name="TextData"&gt;exec praGetTradesForProductIdReassignment @int4_TradeId=NULL,@int4_FeedId=NULL,@int4_SystemId=NULL,@int4_TradeCode=NULL,@int4_ProductID=NULL,@sortColumn=N'',@sortDirection=0,@pageIndex=0,@pageSize=20&lt;/Column&gt;
        &lt;Column id="13" name="Duration"&gt;356352721&lt;/Column&gt;
      &lt;/Event&gt;
    &lt;/Events&gt;
  </code></pre>
  
  <p>Why does the ADO.NET execution take so much longer than the SSMS execution?</p>
  
  <p>The purpose of this question is not to improve the performance of the stored procedure, but rather to determine the reason for the difference in performance between an invocation via ADO.NET vs SSMS.</p>