NHibernate retrieve performance with Oracle (set FetchSize)

[David_L]

I have faced a similar problem like in https://groups.google.com/g/nhusers/c/XLDf_muFWMc/m/IRHDsfcAcOkJ.
A large number of rows and a big amount of data per row reduces the loading performace of queries in NHibernate using Oracle.

Fabio Maulo suggested https://groups.google.com/g/nhusers/c/XLDf_muFWMc/m/9QL5gRczcvcJ. But that solution is not query specific as the FetchSize is set to a constant value again. It would be better to set the FetchSize after creating the DataReader, when the RowSize is calcualted.

Here is my solution (I user OracleManagedDataAccess):

    public class MyOracleManagedDataClientDriver : OracleManagedDataClientDriver, IEmbeddedBatcherFactoryProvider {
        System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass => typeof(MyOracleDataClientBatchingBatcherFactory);
    }

    public class MyOracleDataClientBatchingBatcherFactory : IBatcherFactory {
        public IBatcher CreateBatcher( ConnectionManager connectionManager, IInterceptor interceptor ) {
            return new MyOracleDataClientBatchingBatcher(connectionManager, interceptor);
        }
    }

    public class MyOracleDataClientBatchingBatcher : OracleDataClientBatchingBatcher {
        public MyOracleDataClientBatchingBatcher(ConnectionManager connectionManager, IInterceptor interceptor) : base(connectionManager, interceptor) { }

        public override DbDataReader ExecuteReader( DbCommand cmd ) {
            var reader = base.ExecuteReader( cmd );
            if( reader is OracleDataReader odr ) {
                var newSize = odr.RowSize * 1000; // PrefetchRows = 1000
                if( odr.FetchSize < newSize )
                    odr.FetchSize = newSize;
            }
            return reader;
        }
    }
   
I simply create a derived Batcher that overrides ExecuteReader() and sets the FetchSize for 1000 rows. In most cases this creates a fetch buffer that is not too big but ideal for fetching exact 1000 rows per round trip. This PrefetchRow size is fixed 1000 in my solution but it could be configurable in future.

I had the problem to select 40.000 rows of a table that has a RowSize of 70KB. The default FetchSize of Oracle is set to (very conservative) 128KB which means that each row is fetched in a single round trip. It took 450s to get all rows. With my optimization the retrieval time is reduced to 20s.

Wouldnt it be a good solution to adapt in the standard OracleDataClientBatchingBatcher? It boosted the performance of almost all my queries. Wouldnt it be good for all users of NHibernate and Oracle to have this boost?

[Frédéric Delaporte]

Hi,

You can PR your idea, but likely with a configuration setting for controlling this behavior (like oracle.reader_fetch_rows), and disabled by default (like 0 or not set => no change to the reader fetch size). There is a strong trend going toward reducing memory needs, so enabling by default a feature which augment memory consumption would likely not be accepted.

Since loading hundreds of rows is not a typical usage for an ORM, maybe it is not worth it, and then you would have to stick to your overrides.