ODP.Net Fetch size

4008784

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.

Alex Keh-Oracle

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.

Alex Keh-Oracle

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.