Bad performance with OracleDataReader

Question

I'm experiencing some terrible performance with reading data off the OracleDataReader object compared to MS SQL Server. It is almost 10 times slower, which is unacceptable.

Below is some sample test code that both tests use. What's the most optimum way to read data from OracleDataReader, is there a better way than shown below?

I'm having hard time believing that ODP.Net can't even compare to SqlClient.

UPDATE: I've narrowed down the problem to fetching of text fields. For some reason ODP.Net is terrible at this. Any ideas how to fix it?

void ReadData(System.Data.IDataReader dr, int maxRows)
 {
     ArrayList rows = new ArrayList(maxRows > 0 ? maxRows : 1000);

     object[] row;

     int rowsRead = 0;
     while (dr.Read() && ((maxRows == -1) || (rowsRead++ < maxRows)))
     {
         row = new object[dr.FieldCount];
         dr.GetValues(row);

         rows.Add(row);
     }
     rows.Clear();
 }

Note(s):

• Tried experimenting with FetchSize, didn't experience a big difference

• Query run times aren't the issue here, only the data retrieval.

• The data structures on both databases are identical.

• Tried DataAdapter/DataSet combo with similar results.

Answer 1

We have actually traced this issue down to the use of CLOB columns to store nvarchar(MAX) type of string data.

Oracle has admitted that their OCI software has issues dealing with CLOBs. By default they try to retrieve the CLOB the same way they'd retrieve a very large BLOB. They setup pointers, try to do paging and etc. Of course, this default behavior kills performance when it comes to a regular ~200 char text field. You actually turn this behavior off by setting LOBFetchSize to -1. This way it'll grab the contents of the CLOBs in one round trip. Then things start flying and you get very good performance.

Even with this though we continued to have issues. We confirmed memory leaks and memory reference errors in the OCI software prior to version 11.2. But 11.2 seems to be working fine in both 32 and 64 bit scenarios.

So setting the LOBFetchSize to -1 was the performance fixer here.