Click here to Skip to main content
Click here to Skip to main content
Go to top

OracleDataReader FetchSize Property

, 25 Jul 2012
Rate this:
Please Sign up or sign in to vote.
"Application performance depends on the number of database round-trips to retrieve data. The less round-trips, the better the performance."

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

cesar_boucas
Web Developer
Brazil Brazil
Yesterday C++, today C#.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 25 Jul 2012
Article Copyright 2012 by cesar_boucas
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid