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? -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/f7b3ad26-2d42-4379-9f9f-fc5a277985ffn%40googlegroups.com.
