New efcore provider: Implementing batched update support

[ronnyek]

Question

Greetings! I'm still plugging my way through an efcore implementation for a proprietary database engine. I've made quite a lot of progress, but noticed I'm not seeing batch support happening automatically. I'm not sure this is explicit bulk insert, but rather sending multiple updates delimited by semi colon or something. I've just got a few questions on implementation of this. Article about batched updates

1. Its safe to assume that batched updates as discussed above are different than the bulk insert implementations floating around, right?
2. Do those batched updates handle inserts, and if so... how do people typically handle tracking insert id's for that?
3. For implementing, is it a simple matter of implementing IModificationCommandBatchFactory to handle that?
4. Is there any place anyone can point me to see a reference implementation?

I've got a sample project where I'm simply trying to insert a number of records by AddRangeAsync and then calling SaveChangesAsync and they seem to run as a bunch of different queries on the same connection. For this demo project I've also configured min of 1 and max of 50 for batched statement sizes. I just don't know if batched updates are only for updates, or if they should cover inserts as well.

Thank you for your time, and any pointers you might be able to give

Your code

Stack traces

Verbose output

EF Core version

8.0.11

Database provider

Custom

Target framework

.net 8

Operating system

Windows 11

IDE

Visual Studio 2022

[AndriySvyryd]

Since you mentioned IModificationCommandBatchFactory I'm assuming that you are using an ADO.NET provider to communicate with the database. If so, then #18990 should make implementing this easier in the future.

1. Its safe to assume that batched updates as discussed above are different than the bulk insert implementations floating around, right?

At least in our terminology - yes. Bulk Insert is a highly optimized way of inserting data that doesn't allow retrieving any store-generated values from the inserted rows. Tracked by #27333

2. Do those batched updates handle inserts, and if so... how do people typically handle tracking insert id's for that?

Yes and this is a tricky part. For SQL Server we use MERGE ... INSERT ... OUTPUT ..., however the OUTPUT clause is essentially a SELECT in the sense that it doesn't guarantee that the results are returned in the same order as they were sent to the database. So, we add an artificial ordinal column, just in the scope of the insert, that we can use to ensure that the order is maintained.

See SqlServerUpdateSqlGenerator.cs for details.

3. For implementing, is it a simple matter of implementing IModificationCommandBatchFactory to handle that?

Yes, and choose whether to derive from ReaderModificationCommandBatch.cs or AffectedCountModificationCommandBatch.cs depending on how the number of rows affected is returned by the database.

[ronnyek]

Since you mentioned IModificationCommandBatchFactory I'm assuming that you are using an ADO.NET provider to communicate with the database. If so, then #18990 should make implementing this easier in the future.

Yes, and I think I'll make sure we do that. Is this meant to handle any mutation (insert/update/delete)?

Yes and this is a tricky part. For SQL Server we use MERGE ... INSERT ... OUTPUT ..., however the OUTPUT clause is essentially a SELECT in the sense that it doesn't guarantee that the results are returned in the same order as they were sent to the database. So, we add an artificial ordinal column, just in the scope of the insert, that we can use to ensure that the order is maintained.

I believe this could work the same way, though one thing I'm actually curious about is ensuring db assigned ID's are communicated back to efcore infrastructure correctly (if this is even a requirement). Eg, If I essentially have 10 inserts happening, its easy enough to batch those by delimiting with semicolon. For each insert I can easily capture the newly selected ID, or the id and columns being expected back (using affected rows and scope_identity() which works like sql servers scope_identity(), but is the sql server implementation essentially taking the output which returns insert.* and re-mapping that back to the entities that were told to insert? Is this where the artificial ordinal comes in?

I understand a bit more now as to why this is automatically just creating its own statement per 'batch' because the ModificationCommandBatchFactory is returning a SingularCommandBatch, so I get how to change that to a ModificationCommandBatch I create, I just need to make sure I can actually insert/update and be able to return data from that, that the efcore infrastructure expects (and that may not be possible)

[ronnyek]

@AndriySvyryd Do you know if there are test I can run that would show exactly what is happening with sql server as far as here are x inserts, here is the statement batch sent to sql server and what it returns back? I found ModificationCommandBatch tests and ModificationCommandBatchFactoryTests but from what I can see, it looks like its unit testing statement limits etc. Would this be a good example?

DECLARE @inserted0 TABLE ([Id] int);
INSERT INTO [dbo].[Ducks] ([Id])
OUTPUT INSERTED.[Id]
INTO @inserted0
VALUES (DEFAULT),
(DEFAULT);
SELECT [t].[Id], [t].[Computed] FROM [dbo].[Ducks] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id]);

So essentially declaring a variable of table type, then insert into the table outputting inserted id into that table (in this case with default values), ultimately returning the ids in the order they were inserted?

[AndriySvyryd]

Is this meant to handle any mutation (insert/update/delete)?

Yes, once we implement the support for it in EF (not this year).

but is the sql server implementation essentially taking the output which returns insert.* and re-mapping that back to the entities that were told to insert? Is this where the artificial ordinal comes in?

Yes, SQL Server needs to do it because the generated values are returned as a table. If you use a separate statement for each row there should be no problem with the order. ReaderModificationCommandBatch doesn't have any code to read back the values, but you can look at

efcore/src/EFCore.Relational/Update/AffectedCountModificationCommandBatch.cs

Line 153 in aba8a1b

protected override async Task ConsumeAsync(

It allows reading the results from a single resultset or single row per resultset or anything in between.

@AndriySvyryd Do you know if there are test I can run that would show exactly what is happening with sql server as far as here are x inserts, here is the statement batch sent to sql server and what it returns back?

Take a look at https://github.com/dotnet/efcore/blob/44580352b37ed0df4fa02236c7d344b5bb003ef3/test/EFCore.SqlServer.FunctionalTests/BatchingTest.cs

You could use SSMS to look at the sent SQL.

So essentially declaring a variable of table type, then insert into the table outputting inserted id into that table (in this case with default values), ultimately returning the ids in the order they were inserted?

Yes, pretty much, this is for the most complex case where the keys are generated in addition to other columns

[ronnyek]

Ok, I've been able to determine that I do have a way via proprietary API to execute multiple inserts, and get the inserted ID's back via something like
command.ExecuteBatch()

and access inserted ID's with
command.GeneratedIds which is a datareader that lets me pull out generated fields created for each of the inserts.

Being that I'd have to kind of override what efcore is doing a little lower level, is this something I should still be able to do? From what I've seen so far, most implementations just generate slightly different sql, but I suspect this would need to work all together differently. IIRC, I almost need to format the stuff to insert into a datatable, then call the ExecuteBatch with that... and I assume then map the resulting ID's back to their original entities.

[AndriySvyryd]

Being that I'd have to kind of override what efcore is doing a little lower level, is this something I should still be able to do?

Yes, but you might need to derive from ModificationCommandBatch as you won't be able to use RawSqlCommand

[roji]

The PostgreSQL implementation may be helpful.

[ronnyek]

A couple questions for clarity

1. Does efcore by default intelligently batch statements by entity type or anything?

2. The Records affected modification batch works how? I dont see anyplace where it seems to be taking generated Id's and populating them back onto entities once inserted (or updted for generated fields)

3. Is there an explicit api from the EFCORE consumer, to tell it to run stuff in a batch? I was under the understanding you'd configure it at the provider level, and efcore would try to be smart about batching what it could

4. What if any implications do transactions play here? If you dont explicitly set up transactions, is efcore going to try to process all the statements in 1 batch as one bigger transaction?

5. @roji I'm looking at the pg provider... am I correct in making assumptions that the commandbatch implementation in postgres, is ESSENTIALLY just writing one large statement with some delimiter (like ;) with many parameters... and the ultimately getting a list of records affected, or does it actually return generated id's?

I feel like I must be overthinking this =)

[roji]

Does efcore by default intelligently batch statements by entity type or anything?

No - and there's generally no good reason to do so; for relational (SQL) databases, batching is simply placing multiple SQL statements in a single DbCommand (and therefore batch or roundtrip); there's no particular reason to separate different entity types.

One exception here is if you have a specific optimization around e.g. inserting multiple entities into the same table. For example, the SQL Server provider has an optimization using MERGE for "bulk insert" into the same table, so that groups together all insert operations on the same table. But that's a provider-specific thing.

The Records affected modification batch works how? I dont see anyplace where it seems to be taking generated Id's and populating them back onto entities once inserted (or updted for generated fields)

You're looking for AffectedCountModificationCommandBatch (which extends ReaderModificationCommandBatch), and specifically this line; "PropagateResults" here mean exactly populating generated IDs back into the entity type.

Is there an explicit api from the EFCORE consumer, to tell it to run stuff in a batch? I was under the understanding you'd configure it at the provider level, and efcore would try to be smart about batching what it could

No - this is one implicitly/automatically by SaveChangesAsync. In other words, if it's possible and makes sense for a provider to batch, it should do so.

What if any implications do transactions play here? If you dont explicitly set up transactions, is efcore going to try to process all the statements in 1 batch as one bigger transaction?

The relational implementation wraps the changes performed by a single SaveChangesAsync call in a transaction. There are some tidbits around this (if the SaveChangesAsync produces just one SQL statements, the exception is skipped as single statements are generally already atomic; there's also AutoTransactionBehavior, which allows users to disable the automatic SaveChangesAsync transaction, or to always have one even for single-SQL batches.

I'm looking at the pg provider... am I correct in making assumptions that the commandbatch implementation in postgres, is ESSENTIALLY just writing one large statement with some delimiter (like ;) with many parameters... and the ultimately getting a list of records affected, or does it actually return generated id's?

I'm not sure quite what you're asking here. Yes, batching in relational providers generally means concatenating multiple SQL statements into a single ADO.NET DbComman, with semicolons as delimiters (ADO.NET now has a 1st-class, structured batching API - DbBatch - but EF unfortunately doesn't support that yet).

On the reading side, records affected and generated IDs are two orthogonal things; for example, when doing multiple updates we read back the records affected, but there are generally no generated IDs. Beyond that I'm not sure what you need to know.

[dario-l]

batching is simply placing multiple SQL statements in a single DbCommand (and therefore batch or roundtrip);

@roji
Is there an Opt-In to disable usage of MERGE statement by ef core but not disabling batching?

Can EF Core 9 group many inserts/updates/deletes in one db call without using a MERGE? Some switch? Replacing some ef core component, whatever? 😺

Why? We are notice a lot of deadlocks when MERGE is used. Setting MaxBatchSize to ONE resolves this but we are observing performance degradation because those multiple calls through the wire.

Why Opt-In? Check it out:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Brent Ozar also says:
https://www.youtube.com/watch?v=ZVZAcE4aJPE&t=481s

... and this unfortunately is still valid with current version of SQL Server. 😞

We are using SQL Server 2019 and we do not have much hope for a quick improvement in this matter.

[roji]

@dario-l EF's specific usage of MERGE is generally safe: the source "table" isn't a real table, but incoming parameters sent from the client, and the MERGE only inserts (no upserts). A lot of the issues discussed in your links are relevant to other, more advanced usages of MERGE (especially upserts).

However, we're indeed aware of at least one deadlock that can be reproduced here - see #21899. However, the same deadlock there can be reproduced without MERGE, using simply multi-value insert statements (see #21899 (comment), but I really recommend giving the whole issue a good read).

In other words, the problem is probably not MERGE, but rather the batched multi-value insertion in a highly concurrent scenario; setting MaxBatchSize to 1 doesn't work around it because it removes MERGE, but rather because it removes the batching/multi-value insertion altogether. So then it really is a tradeoff on SQL Server between performance and the possible deadlocking in highly-concurrent scenarios.