Click here to Skip to main content
11,705,617 members (53,745 online)
Click here to Skip to main content

OracleDataReader FetchSize Property

, 25 Jul 2012 CPOL 11.9K 1
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#.

You may also be interested in...

Comments and Discussions

 
QuestionOracleDataReader Pin
DavidBTosh19-May-15 22:30
memberDavidBTosh19-May-15 22:30 
AnswerRe: OracleDataReader Pin
cesar_boucas20-May-15 4:45
membercesar_boucas20-May-15 4:45 
GeneralCesar, you the man!!! Pin
Member 1100118112-Dec-14 5:00
memberMember 1100118112-Dec-14 5:00 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150819.1 | Last Updated 25 Jul 2012
Article Copyright 2012 by cesar_boucas
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid