title: Performance with ADO.NET large data retrieval in ASP.NET Core web app
body: <p><strong>Application</strong> is written in <strong>.NET Core 2.1</strong> and <strong>Database</strong> is <strong>SQL Server 2014</strong>.</p>
  <p>There is a page which has to retrieve 80-100 thousand rows of data from SQL Server. I've added indexes to the columns I deemed appropriate and the query can return all the rows in around 5 seconds. So, as the application has to then iterate all these rows and create view model objects, it takes another 6-8 seconds to loop and do all the logic necessary. This results in a total load time of about 16-17 seconds.</p>
  <p>Before anyone suggests using pagination, this view is designed to look like an Excel worksheet and does a bunch of CSS fun to make it look presentable. As I can't find any more optimization of the loop in the backend and I have compressed the View being returned via IIS Compress Dynamic option, I thought the only other thing to do to maybe shave some time off the load would be to query the database in batches. I tested a run of doing a batch of 10000 at a time and that returned in SQL Management in less than 1 second. So I coded things to fire off a <code>Task&lt;DataTable&gt;</code> that creates it's own database connection and queries for its batch of 10000 records and do this in a loop creating enough tasks to cover the amount of records needing to be returned. This works off a previous query that returns a MIN and a MAX identity value for the given range of records, the range is off of a date range of pay periods. I then do an <code>await Task.WhenAll()</code> on each of these tasks thinking that this would improve the wait time on the initial pull of the data from SQL. I've found that this results in about the same if not worse wait time.</p>
  <p>I'm out of ideas as to how to improve performance at this point and was hoping someone may be able to suggest another idea. I'll provide my <code>Task&lt;DataTable&gt;</code> approach to calling the database for chunks of data so that you can see if I may have done something wrong.</p>
  <pre><code>public async Task&lt;IEnumerable&lt;DataTable&gt;&gt; GetOverViewInfoChunk(List&lt;int&gt; disciplineIds, List&lt;KeyValuePair&lt;string, bool&gt;&gt; statuses, DateTime startWeek, DateTime endWeek, long firstId, long lastId, int batchNumber = 10000)
  {
       DateTime now = DateTime.Now;
       string procName = _config.GetValue&lt;string&gt;(nameof(GetOverViewInfoChunk));
       IList&lt;Task&lt;DataTable&gt;&gt; tablelistTasks = new List&lt;Task&lt;DataTable&gt;&gt;();
       long rangeCounter = firstId + batchNumber - 1;
       IEnumerable&lt;DataTable&gt; tables = new List&lt;DataTable&gt;();
       while (true)
       {
            Dictionary&lt;string, string&gt; @params = new Dictionary&lt;string, string&gt;();
            @params.Add(&quot;@disciplines&quot;, _sqlHelper.GetDisciplinesForTopAndBottom3(disciplineIds).ToString());
            @params.Add(&quot;@startWeekDate&quot;, startWeek.ToString(&quot;yyyy-MM-dd&quot;));
            @params.Add(&quot;@lastWeekDate&quot;, endWeek.ToString(&quot;yyyy-MM-dd&quot;));
            @params.Add(&quot;@jobStatuses&quot;, _sqlHelper.GetJobOverviewStatuses(statuses).ToString());
            @params.Add(&quot;@firstId&quot;, firstId.ToString());
            @params.Add(&quot;@lastId&quot;, rangeCounter &gt; lastId ? lastId.ToString() : rangeCounter.ToString());
            tablelistTasks.Add(new Classes.SQLActionHelper(_config.GetValue&lt;string&gt;(&quot;Data:XXXX&quot;)).GetBatch(procName, @params));
                  
             //Increment range vars
             firstId += batchNumber;
             rangeCounter += batchNumber;
                  
             if (firstId &gt; lastId)
               break;
        }
           
        try
        { 
            tables = await Task.WhenAll(tablelistTasks);
        }
        catch (Exception ex)
        {
  
        }
  
        TimeSpan benchMark = DateTime.Now - now;
        return tables;
  }   
  
  //class for querying the data in batches
  public class SQLActionHelper
  {
      private string _connStr;
  
      public SQLActionHelper(string connStr)
      {
          _connStr = connStr;
      }
  
      public async Task&lt;DataTable&gt; GetBatch(string procName, Dictionary&lt;string, string&gt; @params)
      {
          DataTable dt = null;
          //create the connection object and command object
          using (SqlConnection conn = new SqlConnection(_connStr))
          {
              using (SqlCommand command = new SqlCommand(procName, conn))
              {
                  //Open the connection
                  await conn.OpenAsync();
  
                  //its a stored procedure
                  command.CommandType = System.Data.CommandType.StoredProcedure;
  
                  //Add key value pairs to the command for passing parameters to sql proc or query
                  foreach (KeyValuePair&lt;string, string&gt; kvp in @params)
                  {
                      command.Parameters.AddWithValue(kvp.Key, kvp.Value);
                  }
  
  
                  using (SqlDataReader reader = await command.ExecuteReaderAsync())
                  {
                      dt = new System.Data.DataTable();
                      dt.Load(reader);
                      return dt;
                  }
              }
          }
      }
  }
  </code></pre>