Click here to Skip to main content
15,867,308 members
Articles / Database Development
Tip/Trick

OracleDataReader FetchSize Property

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
25 Jul 2012CPOL1 min read 42.5K   1   6
"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:

C#
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)


Written By
Web Developer
Brazil Brazil
Yesterday C++, today C#.

Comments and Discussions

 
Question[My vote of 2] Updates Pin
Pregunton Cojonero Cabrón17-Jun-16 7:06
Pregunton Cojonero Cabrón17-Jun-16 7:06 
AnswerRe: [My vote of 2] Updates Pin
cesar_boucas17-Jun-16 8:01
cesar_boucas17-Jun-16 8:01 
QuestionOracleDataReader Pin
DavidBTosh19-May-15 22:30
DavidBTosh19-May-15 22:30 
AnswerRe: OracleDataReader Pin
cesar_boucas20-May-15 4:45
cesar_boucas20-May-15 4:45 
GeneralCesar, you the man!!! Pin
Member 1100118112-Dec-14 5:00
Member 1100118112-Dec-14 5:00 
GeneralRe: Cesar, you the man!!! Pin
cesar_boucas12-Apr-16 7:33
cesar_boucas12-Apr-16 7:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.