SqlClient: Implement optimized version of the new ADO.NET batching API

[GSPP]

Edited by @divega on 2/6/2019

We want to add public and provider independent batching APIs to the base classes in ADO.NET. This has been described in detail at https://github.com/dotnet/corefx/issues/35135.

We plan for the batching API to include a "fallback implementation" that should work for most existing providers that simply support the DbCommand functionality. But the fallback implementation will be sub-optimal (it will just concatenate the contents from CommandText into a single string and execute it.

From now on, we are going to use this customer-reported issue to track implementing a SQL Server-optimized version of the batching APIs.

As this issue originally referred to, SqlClient already contain the general functionality necessary in System.Data.SqlClient.SqlCommandSet, but there is no public API to use it directly and the only way to leverage it is through DataSet and SqlDataAdapter.

Original issue contents

-- Command sets are a SQL Server / TDS facility that lets database clients send multiple batches in one round trip. This can be very beneficial for performance. This facility is currently only being used by the SqlDataAdapter infrastructure. This infrastructure is not being used by modern apps anymore and it exposes only a very limited functionality (not arbitrary commands).

Command sets can help with this:

1. Send multiple batches in one round trip
2. Receive results independently
3. Much enhanced performance for executing many small commands
4. A clean API

SqlCommandSet should be cleaned up and made public. There is great community interest and value in doing this (link to a well known post by Ayende, includes benchmarks, second post, Ayende's hack to pry the class open).

Currently, people often concatenate their SQL commands into one batch. This is problematic:

1. It can defeat plan caching
2. It's slower
3. It's tedious and error prone (especially since you need unique parameter names, and errors non-deterministically stop the batch or they don't, and you can't easily find out which command failed)
4. It is subject to a total 2100 parameter limit (add by @roji, 2020-06-22)

I believe that command sets have great value. It looks like cleaning them up would not be too hard since they were written in a style that looks like they were meant to be exposed.

I also believe that the .NET community is not sufficiently aware that there is this super fast and clean way of submitting many small statements. Many applications and scenarios could benefit from this. When this is made public there should be a blog post on a Microsoft blog about it. It really is a secret ninja trick at this point. "Do this one special trick and get much better performance... DB consultants hate it."

Entity Framework could send updates likes that. According to the measurements by Ayende (and my own) this would be a very nice performance boost. I believe EF sometimes sends multiple sub-queries for one query in case of includes. This could make use of command sets as well cutting away one round trip for each query.

In the age of cloud network latencies are typically higher. It becomes especially valuable to be able to cut out round-trips.

[cheenamalhotra]

NET6 build target needs to be added

This is an approved change, and we will be adding it soon after .NET 6 release for the new API support from both .NET 5 and 6. Just that we're expecting some heavy changes in the driver code when building with .NET 6, but keeping fingers crossed 🤞 Prep work for .NET 6 building will be done now, but DLLs will be introduced to our NuGet after .NET 6 release.

Decisions on backporting to <NET6 versions (possible but is it desired?)

We'll have to evaluate the impact and accordingly make a decision to this.

[Wraith2]

We'll have to evaluate the impact and accordingly make a decision to this.

I've got the core working without any net6 so the impact shouldn't be very big. The main decisions is how to provide the local version of the new classes that are added like DbBatchCommand, we can't go without them because we need the base class but if we put it in the main namespace we'll prevent multiple providers being in the same process, I thought putting them in a local Microsoft.Data.Common would work.

We'll have to see what happens with encryption which is going to be need to be done on the MS side.

[Wraith2]

@DavoudEshtehari @JRahnama This is the issue we discussed last week. The api is in net 6 so what we need is a net6 build target and a policy decision from the MS side on the api will be made available in other builds where the base classes aren't available.

@roji to get this onto the management radar to implement we need to get more upvotes. I don't do twitter etc but is there any chance you or any of the EF team could raise the visibility and as anyone who wants the feature to give us an upvote?

[JRahnama]

@Wraith2 we start adding net6 for TFWs (sometime before preview1) and after that we are interested to start reviewing this and take it in also before preview 1.

[Havunen]

Any news from the progress of this task? .NET6 has been active LTS for some time already.

We are facing similiar issue as described here: https://github.com/dotnet/efcore/issues/22852

[Wraith2]

No news.

[Wraith2]

Initial implementation is up: https://github.com/dotnet/SqlClient/pull/1825

Please review, test your use cases, provide (constructive) feedback. I expect there to be at least some further work required and I feel that we could use some crowd sourced tests to provide better behavioural coverage.

[dazinator]

Any more news?

[Wraith2]

The feature was merged. It'll be available in netcore only in all future builds.

[ErikEJ]

@JRahnama Could this be closed?

[JRahnama]

Closing as v5.2.0-preview4 is released with this feature included.

[Wraith2]

@ayende sqlclient batching api is now public

[ErikEJ]

@Wraith2 Just to understand - each SqlBatchCommand supports up to 2098 parameters, is that correct?

[Wraith2]

As far as i know, yes. Please feel free to try it...