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.
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
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.
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
So, i don't want to use DatReader since DB Connections will be open till BusineesLogic is executed.
In this scenario is there any alternative to improve the performance without using DataReader?