ODP.Net Performance Ref Cursor vs VArray vs CLOB

4008784

Hi All,

We are sending refcursor as an output parameter from our oracle procedure and using DataReader to load the data into datatables.

This is giving peformance impact for more number of records.

We are trying with sending VARRAY of the record udt type or CLOB string which has some significant improvement.

For VArray we need to do more work by creating UDT type both in DB and in Dot net code.

What is the better way to improve performance  for huge number of records in ODP.Net?

AndrewSayer

4008784 wrote:Hi All,We are sending refcursor as an output parameter from our oracle procedure and using DataReader to load the data into datatables.This is giving peformance impact for more number of records.We are trying with sending VARRAY of the record udt type or CLOB string which has some significant improvement.For VArray we need to do more work by creating UDT type both in DB and in Dot net code.What is the better way to improve performance for huge number of records in ODP.Net?

The overhead of the ref cursor approach should not get worse as number of rows increases. Memory usage for returning the varray will get worse and worse as result size increases.

It sounds to me like you’re not fetching from the ref cursor in decent size batches. What’s your configuration for fetchSize? And what’s the projected size of a row that you’re returning in your ref cursor (You can figure this out by checking the execution plan with projections, I think there’s also a property of the command that you can log within your own code)? Dividing the first by the second will give you the number of rows you will be fetching per fetch request, you should aim for at least 100 when you need to obtain large amounts of rows.

-edit

FetchSize and rowSize are both properties of the OracleCommand class, I’m not sure how these are populated when your getting a ref cursor but they should exist somewhere. Otherwise, your fetchSize will most likely be constant throughout your program, your friendly DBA can help figure out the projected row size (and hopefully why it’s so big if it is)

4008784

Actually we are not specifying any fetch size. So by default it will take 128 KB. Can i specify the max Fetch size by hard coding the value (ex: 50MB) so that it will fetch all the data at once? Also what will be difference in the Oracle connection persistence between ExecuteNonQuery (VArray) and ExecuteDataReader (which fetches all the data and stores in Datatable). I want to know the impact if huge no of users are using the application at a time and if the connection pool is empty.

Alex Keh-Oracle

You can specify as large FetchSize as you want to as large as ~4GB. A couple things you want to watch out for when specifying especially large fetch sizes.

1) Make sure you have enough client side memory. If you set the fetch size to 50 MB, then each user will be setting aside its own 50 MB even if they use a lot less than 50 MB when retrieving data.

2) Make sure your other network and client settings are aligned with a larger fetch size. For example, the network session data unit on the client or server may be set to 8 KB depending on the Oracle DB version you are using. You may want to increase this size so that it doesn't represent a bottleneck.