Batching Limits

[wjvii]

I have a SQL table with approximately 450 fields and I am trying to batch update it 100 records at a time using EF Core 1.0.1 and SQL Azure but in viewing the Events in Visual Studio 2015 Update 3 Diagnostics Tools it would appear that it is batching them about 4 at a time assuming each time I see ADO.NET in that window it is one Batch. It is also very slow in large part I am guessing because the batch size is so small and I have approximately 40K records to insert.

I am doing an AddRange of the these records, updating a field of about a dozen records in another related table and then executing a SaveChanges.

So my questions are:

Am I interpreting the Events in VS appropriately?
What affects the maximum batch size that EF Core submits at one time when I do not set the MaxBatchSize? As I understand it, each provider has certain maximums, where do I locate those?
What is the maximum MaxBatchSize.
Is there any way to get it to submit the full batch of 100 at one time?
Some design change I can make to speed this up?

Thank you in advance for any insight or advice.

[divega]

@wjvii it is hard to know for sure without looking at your code or at the generated SQL but based on the information that your table has 450 columns and that SQL Server imposes a limit of 2100 parameters per query, I am going to guess that EF Core is automatically splitting the batches every 4 rows.

I cannot think of a good way to achieve what you want without dropping down to lower level ADO.NET APIs and using SQL Server features EF Core doesn't support yet. E.g. we want EF Core to automatically use Table Valued Parameters automatically which should make it very efficient in scenarios like this (see #2484). In the meanwhile you should be able to use TVPs by writing your own ADO.NET calls against the DbConnection and creating them with custom SQL in a migration.

[divega]

Duplicate of #2484.