ODP.Net Fetch size — oracle-tech

    Forum Stats

  • 3,715,918 Users
  • 2,242,907 Discussions
  • 7,845,683 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

ODP.Net Fetch size

4008784
4008784 Member Posts: 11
edited July 2020 in ODP.NET

Hi All,

Just want to know if giving Fetch size in the below code will improve performance if we know the number of records to be fetched ahead.

The below code calls Oracle stored procedure which returns cursor as a output result.

DataTable dt;

.......

cmd.ExecuteNonQuery();

OracleDataReader dr = ((OracleRefCursor)cmd.Parameters["output"].Value).GetDataReader();

dt.Load(dr);

return dt;

Since we are not looping data reader and loading directly to the DataTable, will the data be fetched all at once or it will fetch with default fetch size.

Best Answer

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited July 2020 Accepted Answer

    What FetchSize determines is the amount of data to retrieve per round trip. For performance, you want to minimize the round trip number by increasing FetchSize to capture as much data as possible. You should see a performance improvement if the current result set is much larger than the default FetchSize, usually 125KB.

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited July 2020 Accepted Answer

    What FetchSize determines is the amount of data to retrieve per round trip. For performance, you want to minimize the round trip number by increasing FetchSize to capture as much data as possible. You should see a performance improvement if the current result set is much larger than the default FetchSize, usually 125KB.

Sign In or Register to comment.