Poor INSERT performance with ExecuteNonQuery() and many parameters

[KristianWedberg]

Description

When inserting rows with ExecuteNonQuery() and placeholder parameters, performance
(measured as inserted parameters per second) with the Microsoft.Data.SqlClient v2.1.1
and System.Data.SqlClient providers is progressively impacted from about 50+ parameters
per statement, both for single rows and for multirow batches.

Inserting with 2048 parameters in the statement is up to 7 times slower than with
128 parameters.

The issue is not present when using the System.Data.Odbc provider (or non-SQL Server
providers & databases), and performance is up to 25 times slower (when using 2048 parameters
per statement) than with the ODBC provider.

I've tested with Windows 10, .NET5.0 and a local SQL Server 2019 database, varying the
number of columns from 1 to 1024, and number of rows in each batch from 1 to 1000.

Note: The 'waviness' at 1000 and 2000 parameters is just an effect of the 1000/1024 and 2000/2048
numbers being separated in the graph - see the write-up for details:

The target table is un-indexed and the inserts uses SQL-92 syntax:

INSERT INTO tablename (column-a, [column-b, ...])
VALUES (@r0_c0, [@r0_c1, ...]),
       (@r1_c0, [@r1_c1, ...]),
       ...

While table valued parameters and bulk inserts don't have this issue, they won't
help when inserting a single or just a few very wide rows at a time, so it would be
really useful to have this addressed (or identify any gremlins on my part!)

Please see the full write-up with charts, tables, BenchmarkDotNet info etc. at:

https://github.com/KristianWedberg/sql-batch-insert-performance

Reproduce

Fully runnable source (using BenchmarkDotNet) and instructions at:

https://github.com/KristianWedberg/sql-batch-insert-performance

Expected behavior

Using more parameters (beyond 128) in an insert row or batch should increase throughput,
measured as the number of parameters inserted per second, just like it does with
System.Data.Odbc and other non-SqlClient providers.

Technical details

• Microsoft.Data.SqlClient Version="2.1.1"
• System.Data.Odbc Version="5.0.0"
• System.Data.SqlClient Version="4.8.2"
• OS=Windows 10.0.19042
• .NET Core SDK=5.0.103

[Wraith2]

What is the real world use case for using so many parameters?

[KristianWedberg]

I've personally come across this with:

• Wide rows to capture different stages of a process (think lots of dates and associated data). One real estate company used 450 columns for this.
  • 'Type 3 slowly changing dimensions' is a similar use case
• Wide rows for denormalized data marts
• Multi-row batches to improve performance
  • While table valued parameters and bulk inserts can handle this use case for SQL Server, those techniques are not portable, which is a big draw-back

[Wraith2]

Multi-row batches to improve performance
While table valued parameters and bulk inserts can handle this use case for SQL Server, those techniques are not portable, which is a big draw-back

This is likely to be addressed in .net 6 for most major providers with dotnet/runtime#28633

I'll try to take a look at this when I can get some time but I make no promises.

[KristianWedberg]

Any help on this issue would be greatly appreciated! As a specific example, I discovered this when benchmarking our ETL library since we use it for (portable) batch inserts. For now, we'll have to advise clients to either switch to the ODBC provider, or use the non-portable bulk insert component. Not the end of the world, but certainly less than ideal.

[cheenamalhotra]

Hi @KristianWedberg

Thanks for the comparisons. We'll take a look if some improvements can be made.

[roji]

This is likely to be addressed in .net 6 for most major providers with dotnet/runtime#28633

This is an important point - one of the major incentives for having this new batching API was to remove the need to send so many parameters (which regardless of perf imposed an upper limit, since no more than 2100 parameters can be sent).

[KristianWedberg]

@roji I'm very much looking forward to the upcoming .NET6 batch API. That said:

• It's still many years before we can let go of all <.NET6 frameworks
• The batch API won't help with wide single row inserts

[roji]

It's still many years before we can let go of all <.NET6 frameworks

This is very true, though it's probably going to be possible (and advisable) to expose the batching API even for older TFMs - just without implementing the ADO.NET base classes. This would make the API accessible regardless of which TFM you're using (possibly even .NET Framework); that's my plan for Npgsql/PostgreSQL, in any case.

The batch API won't help with wide single row inserts

This is true - and at that point it's also a question of how common it is to have such a large number of columns etc.

[Wraith2]

This is very true, though it's probably going to be possible (and advisable) to expose the batching API even for older TFMs

The implementation I've got for SqlClient 3.1/5.0 provides versions of the System.Data base classes needed for this so in theory it can be backported to any TFM the library supports.
One thing i hadn't considered is whether multiple drivers would need to live in the same process, I'll give that some thought and see if i can make the support bits internal.

Of course we've still got to do the work to get the feature into 6.0 and then find the time to get it into SqlClient with encryption support, we might want to light a fire under that @roji 😁

[Wraith2]

Just had a quick look. You're using System.Data.SqlClient. Can you try it with Microsoft.Data.SqlClient from this repository and see if the numbers are any better to start with?

[KristianWedberg]

@Wraith2 I'm already using both Microsoft.Data.SqlClient (the "Microsoft" or "MicrosoftSync" results) and System.Data.SqlClient ("System" or "SystemSync" results) in the code and in the results. Their performance is identical.

[Wraith2]

Ah, I see it now. Had to do some fiddling to profile it and broke something. My mistake.

I've found some interesting areas that I was already aware of but were a way down my list of things to investigate. It's mostly string composition and array resizing overhead. As the parameter count goes up it spends more time allocating and re-allocating to grow and there's not a lot the caller can do about that. It can be reworked to avoid intermediate strings and look at pre-sizing of string builders and target arrays in a few places.

Two things stand out that you could do for the benchmark. Can you keep pre-boxed ints that are used as parameter values around? Their boxing and unboxing is a big source of GC activity. I know that isn't reflectve of real world data but in real world scenarios if you have strongly typed input data of limited or frequently known ranges (bool, small ints) keeping cached boxed versions of some values around and using those will gain you some perf.

If possible reusing SqlParameter instances would be good. They're quite large and you're using a lot of them.

[KristianWedberg]

@Wraith2 I've added (and uploaded the code for) using pre-boxed int values to the test.

Here's the original test (boxing of int values, only run 3 batches):

provider	RPS	CPR	NOS	Mean	Error	StdDev	Throughput [params/s]	Throughput [rows/s]
Microsoft	32	64	3	126.91 ms	9.770 ms	0.536 ms	48411	756
System	32	64	3	126.96 ms	6.023 ms	0.330 ms	48392	756
Odbc	32	64	89	161.43 ms	11.006 ms	0.603 ms	1129092	17642

Instead running with pre-boxed int values makes no difference at all:

provider	RPS	CPR	NOS	Mean	Error	StdDev	Throughput [params/s]	Throughput [rows/s]
Microsoft	32	64	3	126.8 ms	23.63 ms	1.30 ms	48469	757
System	32	64	3	125.7 ms	20.04 ms	1.10 ms	48861	763
Odbc	32	64	89	156.0 ms	22.80 ms	1.25 ms	1168274	18254

SqlParameter instances are already reused across all batches in a test case. For the slowest test cases this was however only 3 batches. Increasing _scaleNumberOfStatements by a factor of 10 means the cost of SqlParameter creation is instead amortized over 30 batches (instead of 3). This, together with pre-boxed int values, still makes no difference:

provider	RPS	CPR	NOS	Mean	Error	StdDev	Throughput [params/s]	Throughput [rows/s]
Microsoft	32	64	30	1.260 s	0.1338 s	0.0073 s	48746	762
System	32	64	30	1.259 s	0.1037 s	0.0057 s	48807	763
Odbc	32	64	896	1.549 s	0.1823 s	0.0100 s	1184652	18510

So creating SqlParameters and setting their values is not the bottleneck.

For profiling work, you might set _rowsPerStatements and _columnsPerRows to a single value each and comment out the other providers to get a single test case, and increase _scaleNumberOfStatements sufficiently to get long enough run times.