title: DataTable.Load() vs. DataReader
body: <p>Getting large data around 200MB using stored procedure from Database. Previously used DataTable.Load() method to populate this data in DataTable. But it caused a performance issue and the DataTable is not responding because of size of Data.</p>
  
  <pre><code>Using reader As DbDataReader = cmdHelper.ExecuteReader(CommandBehavior.CloseConnection)
      Using rstResult As New DataTable()
          rstResult.Locale = CultureInfo.InvariantCulture
          rstResult.Load(reader)
          Return rstResult
      End Using
  End Using
  </code></pre>
  
  <p>But Now inorder to improve the performance started using DataReader directly, but since DatReader is connected architecture, the DB connections will be open till the BusinessLogic is done.</p>
  
  <pre><code>Dim cnHelper As New DbConnectionHelper(_strDBConnection)
  Dim cmdHelper As DbCommandHelper = cnHelper.CreateCommandHelper(strSP)
  cmdHelper.Command.CommandType = CommandType.StoredProcedure
  
  Dim reader As DbDataReader = cmdHelper.ExecuteReader(CommandBehavior.CloseConnection)
  Return reader
  </code></pre>
  
  <p>So, i don't want to use DatReader since DB Connections will be open till BusineesLogic is executed.</p>
  
  <p>In this scenario is there any alternative to improve the performance without using DataReader?</p>