Missing Batch Statement Execution

[marco3479]

On behalf of Emmanuel Bouillot, Outscale:

When using tables with many relations EFCore framework will have to make a lot of operations to persist the data in the database. Basically I would have something like one insert for the main table, n inserts for the foreign table and another n inserts for the relation table (if I want to have a Many-To-Many like relationship). If n = 10000 I will then have 1 + 2 * 10000 inserts to be done. The actual version of the .net driver will translate that into 1 + 2 *10000 sql commands that will trigger 2 * (1 + 2 * 10000) roundtrips. For some reason which I don't understand either each command require 1 roundtrip to be "prepared" and one to be actually executed (leading to more latency). With 40002 roundtrips to be done completing the process will then take 40s If the avg latency is 1ms but more than 6min40s if the latency goes up to 10ms.

So technically the driver in it's current implementation won't be able to handle this scenario so it will limit our capacity when making our data model (knowing that we have to exclude any relationship that may include sth like more than even 100 records).

I have then checked with the Npgsql implementation for .NET and for handling this scenario It will basically concatenate each commands to send it in one batch to the remote Sql Instance (then the server process all the commands in a batch mode).

I strongly believe that having something similar would be of great benefit for the overall performance of the driver.