title: Fast in SSMS and slow in the application - Why does it take this DataSet so long to fill?
body: <p>I have a dataset which is filled from a query as follows...</p>
  
  <pre><code>SELECT  DISTINCT ColA, ColB, ColC, ColD, ColE, ColF, dbo.CustomFunction(ColA) AS ColG
  FROM    TableA
      JOIN ViewA ON ColA = ViewColA 
  WHERE   ColB = @P1 AND ColC = @P2 AND ColD = @P3 AND ColE = @P4 
  ORDER BY ColB, ColC DESC, ColA
  </code></pre>
  
  <p>(Query fields etc obfuscated)</p>
  
  <p>I have profiled this query and it returns around 200 rows in 12 seconds running in SSMS. Note that I restarted the server and used the required DBCC commands to ensure that an existing execution plan wasnt used.</p>
  
  <p>However, when I run this query from my .Net application it takes over 30 seconds to fill the dataset and times out on the default ADO.Net command time out of 30 seconds. </p>
  
  <p>If the query runs in 12 seconds, I just cannot see why it should take more than 18 more seconds to fill 200 rows into a dataset. Unless there is something going on here that I dont know about. I imagine that ADO.Net just calls the query, gets the data and populates it.</p>
  
  <p>The population code looks like this (note I have inherited this from another developer)...</p>
  
  <pre><code>DataSet res = new DataSet();
  
      try
      {
          using (SqlDataAdapter da = new SqlClient.SqlDataAdapter())
          {
              var cmd = new SqlClient.SqlCommand();
              String params = FillParameters(cmd, _params, params);
              cmd.CommandText = params + SQL;
              cmd.Connection = conn;
              cmd.Transaction = _transaction;
  
              if (CommandTimeout.HasValue)
              {
                  cmd.CommandTimeout = CommandTimeout.Value;
              }
  
              da.SelectCommand = cmd;
              da.Fill(res);
              return res;
          }
      }
      catch
      {
          throw;
      }
  </code></pre>
  
  <p>Running this in debug, when the fill method is hit the method takes around 50 seconds to complete. This is proved by setting a high time out on the ADO.Net command. I am happy with the performance of the query which I can run consistently in around 12 seconds so why the additional 18+ seconds to populate the dataset?</p>
  
  <p>Is ADO.Net doing something (possibly due to the structure) of this code that means it takes more than 18 seconds to populate the dataset? I have tried setting EnforceConstraints to false and that makes no difference.</p>
  
  <p>One thing to note is that due to the design of this program, more than the required numbers of parameters are fed into sql command. The FillParameters method does this. There are 20 or so "default" parameters that are added to the command but only e.g. 4 are used by this query.</p>
  
  <p>So in summary,</p>
  
  <ul>
  <li><p>What could be happening to make it take 18+ seconds to fill the DS?</p></li>
  <li><p>Is ADO.Net doing something "clever" with my dataset rather than just running the query and populating the data set?</p></li>
  <li><p>Could it be the excessive amount of parameters passed in that is causing the problem.</p></li>
  </ul>
  
  <p>Thanks.</p>