Better API for ADO.NET command batching

ADO.NET currently supports batching several statements in one command by packing them into a single string as follows:

cmd.CommandText =
        "UPDATE message SET text = $text1 WHERE id = 1;" +
        "UPDATE message SET text = $text2 WHERE id = 2";

While workable, it's a problematic API. The PostgreSQL protocol, for example, needs to send each single SQL statement as a separate protocol message, forcing Npgsql to split CommandText by semicolon at the client side, a pretty complex task (need to understand when the semicolon is quoted, etc.).

It would be much better to provide a list-based API, where the user provides, say, an array of strings. A specific provider can still choose to join by semicolon if needed, but this should be done at the provider level rather than by the user.

For backwards compatibility, the CommandText property would still support the legacy semicolon batching mode. A boolean LegacyBatchingMode connection string parameter, true by default, could be turned off to disable it (thereby removing the client-side SQL parsing in Npgsql, etc.).

@roji

@roji Does PostgreSQL support SQL Batching ?

It is used for SQL Batching support.
We sent the whole SQL Statement to Server and sql server will understand that statement.
https://msdn.microsoft.com/en-us/library/vstudio/haa3afyz(v=vs.100).aspx

e.g "select 1; update *...; select 2;"
Sql Server will return 2 results set.
How this will work in PostgreSQL ?

@kkurni

@kkurni, here are some details. PostgreSQL supports two network protocols: simple and extended (here are the detailed docs).

In the simple protocol you can send a single message with "select 1; update *...; select 2;", PostgresSQL will parse out the multiple statements and return 2 result sets. However, the simple protocol has several shortcomings: no binary data, no parameter support, no prepared statements. For these reasons, Npgsql 3.x switched to the extended protocol.

In the extended protocol you send a series of message - Parse, Describe, Bind, Execute, Sync. This allows you to do everything, but you can send only one SQL statement each time. You can still pack multiple messages into a single packet, achieving good network performance.

Npgsql 3.x does allow users to set DbCommand.CommandText to "select 1; update *...; select 2;". However, since it uses the extended protocol it is forced to parse the statements client-side, and send multiple sets of messages in a single packet (2 Parse messages each with a single statement, 2 Describe messages, and so on).

To summarize, PostgreSQL does support SQL batching, but requires you to submit the different SQL statements in separate protocol messages (if you use the extended protocol). Therefore, the ADO.NET batching API, which requires you to pack multiple statements into a single string with semicolon separators, is unsuitable...

Let me know if you need any more details!

@roji

Thanks @roji for providing us with this detail.

This specification seems to be a provider specific.
As there are other providers which is fine to use CommandText e.g SqlClient.
Do you have any other provider which require this ?

If we end up implementing your request, this will be breaking changes for other provider as well.
This will be much simpler for specific provider to override this if they need to.
E.g

    public class MyCommand : DbCommand
    {
        public List<string> CommandList
        {
            get;
            set;
        }

        private string _commandText;
        public override string CommandText
        {
            get { return _commandText; }
            set
            {
                if (IsExtendedQuery(value))
                {
                    throw new InvalidOperationException("Use CommandList for extended Query");
                }
                _commandText = value;
            }
        }

        private bool IsExtendedQuery(string query)
        {
            //check if it is extended query
            return false; //or true
        }
    }

@kkurni

@kkurni, I think I didn't explain myself clearly...

The PostgreSQL extended vs. simple protocol isn't really relevant here, it was just to give more context. The problem is that ADO.NET's current batching API assumes that provider drivers can simply send a single string with multiple SQL statements and semicolons, which the backend will take care of splitting and executing. This simply isn't the case for PostgreSQL (extended protocol).

The solution could simply be to add a string[] CommandTexts property to ADO.NET's DbCommand. The default setter implementation for this property would simply be to join the array on semi-colon and set the DbCommand's CommandText to it - this would allow people to use the new batching API with providers such as SqlClient, without a single change to those providers. In Npgsql's case, however, I would override this property and construct the PostgreSQL protocol messages directly from the array, without any sort of semicolon parsing or joining.

This would provide a structured, fully-flexible batching API while fully preserving all existing ADO.NET providers.

Please let me know if this is unclear in any way.

@roji

Thanks @roji for clarifying this.

by adding string[] CommandTexts will add more complexity for other providers which doesn't need it.
e.g How will you manage CommandTexts and CommandText value ?
Which one will be the source of truth ?

cmd.CommandTexts = new string[] {"select 1;", "select 2;"};
cmd.CommandText = "select 3;";
Assert.Equals(String.Join(cmd.CommandTexts), cmd.CommandText); //this will be false.

We can potentially add a flag like you suggested above boolean LegacyBatchingMode, which will decide CommandText or CommandTexts as source of truth or throw exception if developer set the wrong setter.
But again this will add additional complexity for other providers which doesn't need this.

It will be simpler to add string[] CommandTexts only to provider which need this. E.g PostGreSQL.
Due to specific requirement of PostGreSQL, your custom provider can handle this separately without impacting on other existing provider.

The addition of a CommandTexts property definitely adds a little of complexity (as always when something is added), but I'm not sure this complexity is added to other providers. If, as I proposed above, the default implementation of this property is to simply set and get the CommandText property, then things are extremely simple - there is only one CommandText value, and CommandTexts reads and writes to it). No need for any sort of legacy flag - the last set of either CommandText or CommandTexts will be the definitive one.

So again, unless I'm missing something this adds a useful, more structured batching API without complexifying existing providers in any way.

Of course I could add CommandTexts to Npgsql, but then I'm forcing people to write Npgsql-specific code in order to do batching. The point is that ADO.NET is supposed to be a provider-independent API, but its batching API models things in a way which isn't.

How will CommandTexts read back the value from CommandText ?
Will it need Split CommandText to return them as a list ?
This will push back the complexity from split those CommandText in the first place.

Except that we only allow to SetCommandTexts without able to read them back.
e.g cmd.SetCommandText(string[])

Providing a set-only function definitely could work, as you suggest.

@YoungGah

@YoungGah, please let me know if you have any questions on this. It's extremely problematic to require providers to fully parse SQL only because their database protocol doesn't allow sending semicolon-joined statements.

@roji

@roji, I understand the issue and I agree that the API is not optimal. In the past, we discussed providing a better API (similar to what you proposed) within the owning team, but due to complexity of error handling and other higher priority works, we never got to add the new API. We will evaluate and prioritize this request post V1, but the ask is clear and make sense. We will update on this request after we release V1.

@roji

@roji That's the point. I must use hacky way to do things performant. There should be the way to do this with official standard API.

If batching is not implemented in .net core yet then, maybe, it is a chance to do this properly even if this requires some changes/extensions in API.

@dario-l

@dario-l, unless I'm mistaken you can do batching with the official standard API, as described above - you just pack your multiple SQL statements into a single string and set CommandText to it. So the issue is not that an API is lacking, it's that it's unstructured and therefore problematic.

@roji

@roji Yes, I can but this leads to rebuild query by database server every time it will change and will be not cached. I've tested that scenario.

What do you mean when you say that the query "will change" and have to be rebuilt? If you properly use parameters why would the query have to change?

@id

"UPDATE X SET Y = 0 WHERE ID = @id;UPDATE X SET Y = 1; WHERE ID = @id;" != "UPDATE X SET Y = 0 WHERE ID = @id;"

I have better performance through SqlCommandSet and batching.

@dario-l

@dario-l that's interesting. I wonder if you have the same speed difference if the queries are not identical between them (i.e. two different updates).

In any case, we both agree and are asking for the same thing - a standard, structured API in ADO.NET for batching.

Useful article on SqlClient batching options: https://blogs.msdn.microsoft.com/dataaccess/2005/05/19/does-ado-net-update-batching-really-do-something/ (copied from https://github.com/dotnet/corefx/issues/31070#issuecomment-405393102).

It's important to note that batching via CommandText with semicolons also seems to have some pretty serious drawbacks even for SqlServer, as described here: https://blogs.msdn.microsoft.com/dataaccess/2005/05/19/does-ado-net-update-batching-really-do-something/ (also see dotnet/SqlClient#19).

An ADO.NET batching API would permit exposing SqlCommandSet in a portable way and boost performance there as well.

The SQL Anywhere ADO.NET driver solved this problem quite elegantly:

Create a single DbCommand
Add n*m parameters (with n=number of parameters for a single insert and m=number of rows)
Execute

Example:

cmd.CommandText = "UPDATE message SET text = @text WHERE id = @id";
cmd.Parameters.Add(new SqlParameter("text", "text1");
cmd.Parameters.Add(new SqlParameter("id", 1);
cmd.Parameters.Add(new SqlParameter("text", "text2");
cmd.Parameters.Add(new SqlParameter("id", 2);

EDIT: Added example

One problematic issue with this approach is that it doesn't allow you to batch different statements, only reexecute the same statement with different parameter sets (IIRC the JDBC batching API has the same limitation). One of the goals here is to allow batching of arbitrary statements together (different types of inserts, updates and even selects).

Another limitation is that it's difficult to imagine how information on a specific statement in the batch would be returned. For example, it's important to be able to return the records affected for each individual statement in the batch, but this doesn't seem possible without a more structured API that has an explicit idea of a batch and a contained statement.

Nevertheless the approach above is elegant within the confines of the current API.

Superceded by dotnet/corefx#35135

Navigation Menu

Repository navigation

Better API for ADO.NET command batching #15375

20 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Better API for ADO.NET command batching #15375

Description

Activity

kkurni commented on Oct 30, 2015

roji commented on Oct 30, 2015

kkurni commented on Nov 3, 2015

roji commented on Nov 3, 2015

kkurni commented on Nov 3, 2015

roji commented on Nov 3, 2015

kkurni commented on Nov 3, 2015

roji commented on Nov 3, 2015

roji commented on Dec 1, 2015

YoungGah commented on Dec 1, 2015

20 remaining items

dario-l commented on Dec 12, 2017

roji commented on Dec 13, 2017

dario-l commented on Dec 13, 2017

roji commented on Dec 13, 2017

dario-l commented on Dec 14, 2017

roji commented on Dec 14, 2017

roji commented on Dec 9, 2018

roji commented on Dec 9, 2018

fubar-coder commented on Jan 30, 2019

roji commented on Jan 30, 2019

roji commented on Feb 6, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions