OracleDataReader FetchSize Property

Our application loads a big cache of data from database when it starts. A DataReader is an option we have chossen to retrive the date, since it’s a set of read-only, forward-only operations.

I observed that to load an Oracle 11g table with 2 million rows and 20 columns the performance increased a lot by reducing the number of round-trips to database while using DataReader.

Following is the example of Employee Table which contains huge number of records.

var connection = new OracleConnection(myConnectionString); 
const string sql = @"SELECT * FROM Employee"; 
var command = new OracleCommand(sql, connection)

{

       CommandType = CommandType.Text

};

var dataTable = new DataTable("Employee");

using (var oracleReader = command.ExecuteReader())

{

oracleReader.FetchSize = (1000 * command.RowSize);

         dataTable.Load(oracleReader);

}

return dataTable;