NHibernate multi query / futures with Oracle

Question

I am trying to use futures in NHibernate 3.2 and Oracle 11gR2. This doesn't seem to be supported although I'm not sure. I found this issue on NHibernate Jira that makes it seem like futures are possible with Oracle. Does anyone know how to get futures to work with Oracle? What exactly is the reason that Oracle isn't supported?

Update

Based on comments here, I tried using HQL multiquery. I got an exception while performing _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 the way how I made NHibernate Future queries work with Oracle. You can just add the below two classes EnhancedOracleDataClientDriver and EnhancedOracleResultSetsCommand to your project and configure NHibernate to use the EnhancedOracleDataClientDriver class as the database driver. I would appreciate feedback whether this approach works for other people. Here is the source code of the mentioned 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 posted an answer to the "NHibernate multi query / futures with Oracle" question with a solution how to make future queries work with Oracle. It was as simple as adding two derived classes EnhancedOracleDataClientDriver and EnhancedOracleResultSetsCommand to a project, and configuring NHibernate to use the EnhancedOracleDataClientDriver class as the database driver.

I recently checked this issue https://nhibernate.jira.com/browse/NH-2170, and figured out that out-of-box NHibernate still does not support futures with Oracle. Plus, I got a question from Ruben on StackOverflow if I can share any sources and/or methodology about deriving this "Enhanced" implementation approach. Plus, some people tested this "Enhanced" approach and were disappointed with the fact that no performance boost noticed as with SQL Server futures.

So I decided to spend some time and revisit this issue trying to profile and optimize the "Enhanced" approach.

Here are my findings with the profiler:

1. In Oracle.ManagedDataAccess provider, the implementation of parameter binding by name is slower than positional parameter binding. I tested a multi-criteria with overall 500 named parameters, and the profiler showed me that before executing the command Oracle provider spent nearly 1 second just for converting named parameters into positional ones. I believe that even regular (non-future) queries with like 500 named parameters would experience similar performance penalties. So, one bottleneck is "command.BindByName = true;".
2. When combining multiple queries into one batch, the SqlStringBuilder.Add(...) should be used (not SqlString.Append(...)). This is the same as with combining strings: StringBuilder performs way better than String.

So, after carefully analysing NHibernate source code where SQL commands are constructed and combined into batches, I came out with the version #2 of the "Enhanced" approach. I hope that NHibernate core team will notice this and consider adding futures with Oracle to my favourite ORM.

By the way, the "Enhanced" approach depends on Oracle refcursors (one output refcursor for each query in a batch), and there is Oracle limitation of max cursors per session that we must be aware of (default on Oracle XE is max 300 cursors).

Usage. Add the below two classes EnhancedOracleManagedDataClientDriver and EnhancedOracleManagedResultSetsCommand 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 cannot comment the answer above :) so my feedback for above implementation goes here: Works good for me, but there is no boost in performance. My test case performs 100 trivial selects on small table on remote Oracle machine and usage of ToFuture<> vs. ToList<> provides similar response time. Comparing to MS SQL on remote machine, ToFuture<> provides about two times shorter response time than ToList<>.