NHibernate Multiuser Query / Futures with Oracle

Question

I am trying to use futures in NHibernate 3.2 and Oracle 11gR2. This does not seem to be supported, although I'm not sure. I found this problem in NHibernate Jira, which makes it possible how futures are possible with Oracle. Does anyone know how to get futures for working with Oracle? What is the reason Oracle is not supported?

Update

Based on the comments here, I tried using a multiscreen multilingual interface. I got an exception while executing _nhSession.CreateMultiQuery(); Here's the exception:

 The driver NHibernate.Driver.OracleDataClientDriver does not support multiple queries. 

What else can I try? Am I using the wrong driver?

Answer 1

I would like to share how I made NHibernate Future queries with Oracle. You can simply add the following two classes EnhancedOracleDataClientDriver and EnhancedOracleResultSetsCommand to your project and configure NHibernate to use the EnhancedOracleDataClientDriver class as the database driver. I would appreciate feedback on whether this approach is suitable for other people. Here is the source code for these classes.

EnhancedOracleDataClientDriver.cs

 using NHibernate.Engine; namespace NHibernate.Driver { public class EnhancedOracleDataClientDriver : OracleDataClientDriver { public override bool SupportsMultipleQueries { get { return true; } } public override IResultSetsCommand GetResultSetsCommand(ISessionImplementor session) { return new EnhancedOracleResultSetsCommand(session); } } } 

EnhancedOracleResultSetsCommand.cs

 using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Reflection; using NHibernate.Engine; using NHibernate.SqlCommand; using NHibernate.SqlTypes; using NHibernate.Util; namespace NHibernate.Driver { public class EnhancedOracleResultSetsCommand : BasicResultSetsCommand { private const string driverAssemblyName = "Oracle.DataAccess"; private SqlString sqlString = new SqlString(); private int cursorCount = 0; private readonly PropertyInfo oracleDbType; private readonly object oracleDbTypeRefCursor; public EnhancedOracleResultSetsCommand(ISessionImplementor session) : base(session) { System.Type parameterType = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleParameter", driverAssemblyName, false); oracleDbType = parameterType.GetProperty("OracleDbType"); System.Type oracleDbTypeEnum = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleDbType", driverAssemblyName, false); oracleDbTypeRefCursor = System.Enum.Parse(oracleDbTypeEnum, "RefCursor"); } public override void Append(ISqlCommand command) { Commands.Add(command); sqlString = sqlString.Append("\nOPEN :cursor") .Append(Convert.ToString(cursorCount++)) .Append("\nFOR\n") .Append(command.Query).Append("\n;\n"); } public override SqlString Sql { get { return sqlString; } } public override IDataReader GetReader(int? commandTimeout) { var batcher = Session.Batcher; SqlType[] sqlTypes = Commands.SelectMany(c => c.ParameterTypes).ToArray(); ForEachSqlCommand((sqlLoaderCommand, offset) => sqlLoaderCommand.ResetParametersIndexesForTheCommand(offset)); sqlString = sqlString.Insert(0, "\nBEGIN\n").Append("\nEND;\n"); var command = batcher.PrepareQueryCommand(CommandType.Text, sqlString, sqlTypes); if (commandTimeout.HasValue) { command.CommandTimeout = commandTimeout.Value; } BindParameters(command); for (int cursorIndex = 0; cursorIndex < cursorCount; cursorIndex++) { IDbDataParameter outCursor = command.CreateParameter(); oracleDbType.SetValue(outCursor, oracleDbTypeRefCursor, null); outCursor.ParameterName = ":cursor" + Convert.ToString(cursorIndex); outCursor.Direction = ParameterDirection.Output; command.Parameters.Add(outCursor); } return new BatcherDataReaderWrapper(batcher, command); } } } 

Answer 2

Three years ago, I sent an answer to the question "NHibernate multi query / futures with Oracle" with a decision on how to make future queries with Oracle. It was as simple as adding two derived classes of EnhancedOracleDataClientDriver and EnhancedOracleResultSetsCommand to the project and setting up NHibernate to use the EnhancedOracleDataClientDriver class as the database driver.

I recently checked this question https://nhibernate.jira.com/browse/NH-2170 and found out that the finished NHibernate still does not support Oracle futures. Also, I had a question from Ruben on StackOverflow if I can share any sources and / or methodology for getting this “advanced” implementation approach. In addition, some people tested this “advanced” approach and were disappointed with the fact that performance improvements were not seen as with SQL Server futures.

So I decided to spend some time and rethink this problem, trying to profile and optimize the Enhanced approach.

Here are my findings with the profiler:

• In the Oracle.ManagedDataAccess provider, the implementation of parameter binding by name is slower than parameter positioning binding. I tested several criteria with a total of 500 parameters, and the profiler showed me that before the execution, the Oracle team spent almost 1 second converting the named parameters into positional ones. I believe that even regular (not future) requests with the same 500 named parameters are similar to penalties. Thus, one bottleneck is "command.BindByName = true;".
• When combining several requests into one batch, SqlStringBuilder.Add (...) should be used (not SqlString.Append (...)). This is the same as concatenating strings: StringBuilder performs a better path than String.

So, having carefully analyzed the source code of NHibernate, in which SQL commands are built and bundled, I came out with version 2 of the "Advanced" approach. I hope the main NHibernate team will notice this and consider adding Oracle futures to my favorite ORM.

By the way, the Enhanced approach depends on Oracle recursors (one returned refcursor for each request in the batch version), and there is an Oracle limit on the maximum cursors per session that we should be aware of (by default there are a maximum of 300 cursors for Oracle XE).

Using. Add two EnhancedOracleManagedDataClientDriver and EnhancedOracleManagedResultSetsCommand classes to your project and configure NHibernate to use the EnhancedOracleManagedDataClientDriver class as the database driver.

EnhancedOracleManagedDataClientDriver.cs

 using System; using System.Data; using System.Reflection; using NHibernate.Engine; using NHibernate.SqlTypes; using NHibernate.Util; namespace NHibernate.Driver { public class EnhancedOracleManagedDataClientDriver : OracleManagedDataClientDriver { private readonly PropertyInfo _oracleCommandBindByName; private readonly PropertyInfo _oracleDbType; private readonly object _oracleDbTypeRefCursor; public EnhancedOracleManagedDataClientDriver() { _oracleCommandBindByName = ReflectHelper.TypeFromAssembly( "Oracle.ManagedDataAccess.Client.OracleCommand", "Oracle.ManagedDataAccess", true).GetProperty("BindByName"); _oracleDbType = ReflectHelper.TypeFromAssembly( "Oracle.ManagedDataAccess.Client.OracleParameter", "Oracle.ManagedDataAccess", true).GetProperty("OracleDbType"); var enumType = ReflectHelper.TypeFromAssembly( "Oracle.ManagedDataAccess.Client.OracleDbType", "Oracle.ManagedDataAccess", true); _oracleDbTypeRefCursor = Enum.Parse(enumType, "RefCursor"); } public override bool SupportsMultipleQueries => true; public override IResultSetsCommand GetResultSetsCommand(ISessionImplementor session) { return new EnhancedOracleManagedResultSetsCommand(session); } protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType) { // this "exotic" parameter type will actually mean output refcursor if (sqlType.DbType == DbType.VarNumeric) { dbParam.ParameterName = FormatNameForParameter(name); dbParam.Direction = ParameterDirection.Output; _oracleDbType.SetValue(dbParam, _oracleDbTypeRefCursor, null); } else base.InitializeParameter(dbParam, name, sqlType); } protected override void OnBeforePrepare(IDbCommand command) { base.OnBeforePrepare(command); if (command.CommandText.StartsWith("\nBEGIN -- multi query\n")) { // for better performance, in multi-queries, // we switch to parameter binding by position (not by name) this._oracleCommandBindByName.SetValue(command, false, null); command.CommandText = command.CommandText.Replace(":p", ":"); } } } } 

EnhancedOracleManagedResultSetsCommand.cs

 using System.Data; using System.Linq; using NHibernate.Engine; using NHibernate.Impl; using NHibernate.Loader.Custom; using NHibernate.Loader.Custom.Sql; using NHibernate.SqlCommand; using NHibernate.SqlTypes; using NHibernate.Type; namespace NHibernate.Driver { public class EnhancedOracleManagedResultSetsCommand : BasicResultSetsCommand { private readonly SqlStringBuilder _sqlStringBuilder = new SqlStringBuilder(); private SqlString _sqlString = new SqlString(); private QueryParameters _prefixQueryParameters; private CustomLoader _prefixLoader; public EnhancedOracleManagedResultSetsCommand(ISessionImplementor session) : base(session) {} public override SqlString Sql => _sqlString; public override void Append(ISqlCommand command) { if (_prefixLoader == null) { var prefixQuery = (SqlQueryImpl)((ISession)Session) // this SQL query fragment will prepend every SELECT query in multiquery/multicriteria .CreateSQLQuery("\nOPEN :crsr \nFOR\n") // this "exotic" parameter type will actually mean output refcursor .SetParameter("crsr", 0, new DecimalType(new SqlType(DbType.VarNumeric))); _prefixQueryParameters = prefixQuery.GetQueryParameters(); var querySpecification = prefixQuery.GenerateQuerySpecification(_prefixQueryParameters.NamedParameters); _prefixLoader = new CustomLoader(new SQLCustomQuery(querySpecification.SqlQueryReturns, querySpecification.QueryString, querySpecification.QuerySpaces, Session.Factory), Session.Factory); } var prefixCommand = _prefixLoader.CreateSqlCommand(_prefixQueryParameters, Session); Commands.Add(prefixCommand); Commands.Add(command); _sqlStringBuilder.Add(prefixCommand.Query); _sqlStringBuilder.Add(command.Query).Add("\n;\n\n"); } public override IDataReader GetReader(int? commandTimeout) { var batcher = Session.Batcher; var sqlTypes = Commands.SelectMany(c => c.ParameterTypes).ToArray(); ForEachSqlCommand((sqlLoaderCommand, offset) => sqlLoaderCommand.ResetParametersIndexesForTheCommand(offset)); _sqlStringBuilder.Insert(0, "\nBEGIN -- multi query\n").Add("\nEND;\n"); _sqlString = _sqlStringBuilder.ToSqlString(); var command = batcher.PrepareQueryCommand(CommandType.Text, _sqlString, sqlTypes); if (commandTimeout.HasValue) command.CommandTimeout = commandTimeout.Value; BindParameters(command); return new BatcherDataReaderWrapper(batcher, command); } } } 

Answer 3

I can’t comment on the answer above :), so my feedback for the implementation above is: “Works fine for me, but there is no performance improvement. My test case performs 100 trivial samples on a small table on a remote Oracle machine, and using ToFuture <> vs. ToList <> provides similar response time. Compared to MS SQL on a remote machine, ToFuture <> provides approximately two times less response time than ToList <>.