OracleDataReader FetchSize Property

Introduction

"Application performance depends on the number of database round-trips to retrieve data. The less round-trips, the better the performance."

Background

Consider an application that loads a big cache of data from database when it starts. A DataReader is a natural choice to do this, since its a set of read-only, forward-only operations.

I observed that to load an Oracle 11g table with 3 million rows and 30 columns the performance increased a lot by reducing the number of round-trips made to provide data to the DataReader.

Using the code

OracleDataReader FetchSize Property

Applications that do not know the exact size of a row at design time can use the FetchSize property of the OracleDataReader object. This FetchSize property should be set after invoking the ExecuteDataReader method of the OracleCommand object and before the first row is fetched using the Read method on the OracleDataReader object.

The RowSize property of the OracleCommand object is set by execution of the ExecuteDataReader. The RowSize property can then be used to set the value of the FetchSize property on the OracleDataReader object. This provides a dynamic way of setting the FetchSize property on the OracleDataReader based on the size of a row:

OracleConnection myConnection = new OracleConnection(myConnectionString);
OracleCommand myCommand = new OracleCommand(mySelectQuery, myConnection);
myConnection.Open();
using (OracleDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
    // here goes the trick
    // lets get 1000 rows on each round trip
    reader.FetchSize = reader.RowSize * 1000;

    while (reader.Read())
    {
        // reads the records normally
    }
}// close and dispose stuff here

Points of Interest

I made some experiments using this approach on Oracle 11g database. And I believe that it could bring benefits on other relational databases too.

Reference

http://docs.oracle.com/html/A96160_01/features.htm#1055500.