SqlClientBatchingBatcher doesn't work with MicrosoftDataSqlClientDriver

[tompazourek]

When using the MicrosoftDataSqlClientDriver, I'm unable to use SqlClientBatchingBatcher.

There's specificaly code that expects that the SqlCommand will be System.Data.SqlClient.SqlCommand which doesn't work well with Microsoft.Data.SqlClient.SqlCommand.

Is there some way to work around this? Can this be fixed, please?

Specific line: https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/AdoNet/SqlClientBatchingBatcher.cs#L67

See _currentBatch.Append((System.Data.SqlClient.SqlCommand) Driver.UnwrapDbCommand(batchUpdate)); in the method:

public override void AddToBatch(IExpectation expectation)
{
    _totalExpectedRowsAffected += expectation.ExpectedRowCount;
    var batchUpdate = CurrentCommand;
    Driver.AdjustCommand(batchUpdate);
    string lineWithParameters = null;
    var sqlStatementLogger = Factory.Settings.SqlStatementLogger;
    if (sqlStatementLogger.IsDebugEnabled || Log.IsDebugEnabled())
    {
        lineWithParameters = sqlStatementLogger.GetCommandLineWithParameters(batchUpdate);
        var formatStyle = sqlStatementLogger.DetermineActualStyle(FormatStyle.Basic);
        lineWithParameters = formatStyle.Formatter.Format(lineWithParameters);
        _currentBatchCommandsLog.Append("command ")
            .Append(_currentBatch.CountOfCommands)
            .Append(":")
            .AppendLine(lineWithParameters);
    }
    if (Log.IsDebugEnabled())
    {
        Log.Debug("Adding to batch:{0}", lineWithParameters);
    }
    _currentBatch.Append((System.Data.SqlClient.SqlCommand) Driver.UnwrapDbCommand(batchUpdate));

    if (_currentBatch.CountOfCommands >= _batchSize)
    {
        ExecuteBatchWithTiming(batchUpdate);
    }
}

[fredericDelaporte]

SqlClientBatchingBatcher uses System.Data.SqlClient, it cannot work with Microsoft.Data.SqlClient.

This batcher is implemented through an internal batcher provided by System.Data.SqlClient, see here.

For Microsoft.Data.SqlClient, use the generic batching batcher. (Just leave it un-configured, the driver will use it by default, see here.)

[tompazourek]

I think it's possible to create a new MicrosoftDataSqlClientBatchingBatcher. The internals that the existing SqlClientBatchingBatcher uses from System.Data.SqlClient are the same in Microsoft.Data.SqlClient, only from different namespace and assembly.

Why do you think it cannot work? Should I prepare a PR to demonstrate?

For Microsoft.Data.SqlClient, use the generic batching batcher. (Just leave it un-configured, the driver will use it by default, see here.)

I'm in the process of migrating a big application from System.Data.SqlClient to Microsoft.Data.SqlClient due to some security reasons. If I change it to the generic batching batcher, there will be a performance regression, will it not? As I understand it, the goal of SqlClientBatchingBatcher is to make the batching faster.

[tompazourek]

If you'd prefer to kill this batcher and not support it in the newer Microsoft.Data.SqlClient, I can understand that.

I'm afraid I'm not able to help with the performance comparison, but I sure would be interested to see some results in case it would be interesting topic for someone to explore.

Thanks for your time and for discussing this with me.

[roken]

There is a very material degradation to moving to the generic batching approach because they operate at two different levels.

The SqlClientBatchingBatcher is batching down at the RPC level when talking to the database, effectively shoving multiple unique commands into the same network packet. MS SQL Server is able to process these independent commands efficiently and minimize the communication overhead. It feels like an oversight to dismiss the idea of using implementation-specific optimizations as legacy.

Conversation within .NET is happening around here: dotnet/SqlClient#1825

The new batching API is being introduced via DbBatch, SqlBatch in the new SqlClient stack.

The concatenation of the commands, as GenericBatchingBatcher does, with large numbers of parameters hammers the database pretty substantially in our usage. We're getting 500-1000% worse performance using the Generic Batching under .NET 6.

Our experience:

Large INSERT statements with 68 values, client and database housed together on the same network.

Runtime	Batching	Time to process	Comment
.NET 4.8	NHibernate Batching = 100	40-50ms	This uses SqlClientBatchingBatcher by default
.NET 4.8	No NHibernate Batching	60-80ms	This demonstrates the claimed 50% perf improvement Ayende made, which is where all this code originates from
.NET 6	NHibernate Batching = 100	280-300ms	Slow, and database is visible angry with this too
.NET 6	No NHibernate Batching	60-80ms	Same as 4.8 with no batching, which makes sense but good to confirm
.NET 6	NHibernate Batching set = 5	70-200ms	Still worse than not batching
.NET 6	NHibernate Batching = 2	60-80ms	No improvement

I could not find any batch size where the generic batching provides any improvement, and in fact it only harms performance as batch size goes up. Our approach here is to simply disable batching in NHibernate.

[gliljas]

Work is ongoing to address this in #3461 . I'll add a bit of effort to make it more than a draft.

[roken]

Amazing, I missed that! I started poking at it but will put that on the backburner and look at this PR

[fredericDelaporte]

Note that there is a test which is concluding there is a performance improvement with the generic batcher similar to the native batcher with SQL Server, here. This test was was done with the SQL Server on the same machine, though.