Which RDBMS has the fastest .NET Bulk Insert implementation?

The Bulk Insert support in the latest release of OrmLite enables accessing different efficient ways made available for each RDBMS for inserting large amounts of data from a .NET App, encapsulated behind OrmLite's new BulkInsert API:

db.BulkInsert(rows);

Bulk Insert Implementations​

Where the optimal implementation for each RDBMS utilize the different available RDBMS-specific implementations below:

• PostgreSQL - Uses PostgreSQL's COPY command via Npgsql's Binary Copy import
• MySql - Uses MySqlBulkLoader feature where data is written to a temporary CSV file that's imported directly by MySqlBulkLoader
• MySqlConnector - Uses MySqlConnector's MySqlBulkLoader implementation which makes use of its SourceStream feature to avoid writing to a temporary file
• SQL Server - Uses SQL Server's SqlBulkCopy feature which imports data written to an in-memory DataTable
• SQLite - SQLite doesn't have a specific import feature, instead Bulk Inserts are performed using batches of Multiple Rows Inserts to reduce I/O calls down to a configurable batch size
• Firebird - Is also implemented using Multiple Rows Inserts within an EXECUTE BLOCK configurable up to Firebird's maximum of 256 statements

SQL Multiple Row Inserts​

An efficient and compact alternative to inserting large amounts of data that enjoys broad support from all RDBMS's is to use SQL's Multiple Insert Rows feature to insert multiple rows within a single INSERT statement:

INSERT INTO Contact (Id, FirstName, LastName, Age) VALUES 
(1, 'John', 'Doe', 21),
(2, 'Jane', 'Doe', 27),
(3, 'Jack', 'Doe', 42);

Normally OrmLite APIs uses parameterized statements however for SQL Multiple Row Inserts it uses inline rasterized values in order to construct and send large SQL INSERT statements that avoids RDBMS's max parameter limitations, which if preferred can be configured to be used instead of its default optimal Bulk Insert implementation:

db.BulkInsert(rows, new BulkInsertConfig {
    Mode = BulkInsertMode.Sql
});

Batch Size​

Multiple Row Inserts are sent in batches of 1000 (Maximum for SQL Server), Firebird uses a maximum of 256 whilst other RDBMS's can be configured to use larger batch sizes:

db.BulkInsert(rows, new BulkInsertConfig {
    BatchSize = 1000
});

Benchmarks​

All benchmarks were run with BenchmarkDotNet running on an macOS M2 Apple Macbook, an Ubuntu 22.04 Linux VM and an Windows 10 Intel iMac 5K.

Hopefully these benchmarks are informative in showing the performance you can expect from Bulk Inserts for each popular RDBMS's across on different operating systems. For context, these benchmarks were run on the configurations below:

Apple M2 / 24GB RAM​

• macOS / ARM
• .NET 6.0 / ARM
• RDBMS run from local Docker containers

Linux VM / 7GB RAM​

• Ubuntu 22.04 LTS / x64
• .NET 6.0 / x64
• RDBMS run from local Docker containers

Intel iMac 5K / 24GB RAM​

• Windows 10 / x64
• .NET 6.0 / x64
• RDBMS run from local Windows installations

Performance is also affected by the quality and efficiency of the ADO .NET library implementations specific to each RDBMS, which can result in non .NET languages yielding different results based on the quality of their data access implementations.

Apple M2 Benchmarks​

Apple M2 Benchmarks were run on an Apple M2 Macbook Air 15" / 24GB RAM, specifications reported by BenchmarkDotNet:

BenchmarkDotNet v0.13.6, macOS Ventura 13.4.1 (22F2083) [Darwin 22.5.0]
Apple M2, 1 CPU, 8 logical and 8 physical cores
.NET SDK 6.0.411
  [Host]     : .NET 6.0.19 (6.0.1923.31806), Arm64 RyuJIT AdvSIMD
  Job-VQWEED : .NET 6.0.19 (6.0.1923.31806), Arm64 RyuJIT AdvSIMD

All benchmarks were run against local databases with most RDBMS's installed and running from a Docker container.

All Docker containers runs native ARM Builds whilst SQL Server's AMD64 Docker image runs emulated on Rosetta using Apple's Virtualization Framework.

Optimized Bulk Insert Performance​

These benchmarks below uses the default optimal Bulk Insert implementation for each RDBMS:

Effectively showing PostgreSQL binary COPY is the fastest Bulk Insert implementation of all RDBMS providers, we're also seeing the overhead of running SQL Server's AMD64 images on ARM having a significant impact on its performance.

Multiple Inserts Rows Performance​

These benchmarks show the performance of executing Multiple Row Inserts in batches of 1000 which is a good example to show the comparative performance of each RDBMS for executing large SQL Insert statements:

Takeaways from these results show PostgreSQL continuing to be a star performer, consistently out-performing other distributed RDBMS's whilst SQL Server yields even worse comparative performances.

Single Insert Performance​

This benchmark measures the performance of multiple single inserts, i.e. using the traditional Single Insert ORM APIs when Bulk Insert is not available:

Ubuntu Linux VM Benchmarks​

The Ubuntu Linux VM Benchmarks were run on a GitHub Actions VM which are reported to run on a 2 Core CPU with 7 GB of RAM, specifications reported by BenchmarkDotnet:

BenchmarkDotNet v0.13.6, Ubuntu 22.04.2 LTS (Jammy Jellyfish)
Intel Xeon Platinum 8370C CPU 2.80GHz, 1 CPU, 2 logical and 2 physical cores
.NET SDK 6.0.412
  [Host]     : .NET 6.0.20 (6.0.2023.32017), X64 RyuJIT AVX2
  Job-HXEVFT : .NET 6.0.20 (6.0.2023.32017), X64 RyuJIT AVX2

Optimized Bulk Insert Performance​

These benchmarks below uses the default optimal Bulk Insert implementation for each RDBMS:

Here we see that SQL Server AMD64 image is not just slow under Rosetta/ARM, it's just consistently slow on Linux - surprising how much slower a multi-billion dollar commercial database is vs the Free and Open Source PostgreSQL.

Multiple Inserts Rows Performance​

These benchmarks show the performance of executing Multiple Row Inserts in batches of 1000 rows:

Single Insert Performance​

This benchmark measures the performance of multiple single inserts:

Here we see overhead cost of multiple I/O calls being significantly slower than an In Memory SQLite database whilst PostgreSQL continues to be a standout performer vs other RDBMS's.

Linux Benchmarks Takeaways​

It's not clear if SQL Server's poor performance is due to having a suboptimal Linux port, running from Docker or running in a Linux VM with only 7GB RAM.

Either way if you're running SQL Server on Linux you may want to consider running your own load tests to check you're not getting poor performance in your environment and whether or not you're better off running SQL Server on a Windows instance.

Intel iMac 5K Benchmarks​

The Intel iMac 5K / 24GB RAM benchmarks were run on Windows 10, specifications reported by BenchmarkDotNet:

BenchmarkDotNet v0.13.6, Windows 10 (10.0.19045.3208/22H2/2022Update)
Intel Core i7-7700K CPU 4.20GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores
.NET SDK 7.0.203
  [Host]     : .NET 6.0.20 (6.0.2023.32017), X64 RyuJIT AVX2
  Job-ILIRNE : .NET 6.0.20 (6.0.2023.32017), X64 RyuJIT AVX2

With all benchmarks running against local databases, installed using their native Windows Installers.

Optimized Bulk Insert Performance​

These benchmarks below uses the default optimal Bulk Insert implementation for each RDBMS:

Here we see that PostgreSQL continue to take the Bulk Insert crown platform even on Windows and SQL Server is also a good performer now that it's running on its native Windows/Intel platform.

It also highlights a significant performance of SQLite disk write performance on Windows/x64 which may be due to how macOS implements fsync.

Relative performance of ARM vs Intel​

Whilst they're not directly comparable with their different configurations, their relative performance numbers provides some indication of how much faster my new Macbook Air M2 is compared to my primary Intel iMac 5K Desktop, even with the overhead of running RDBMS's from within Docker containers:

Where other than SQL Server's poor performance on Linux/Rosetta, the benchmarks run much faster on Apple M2 Macbook Air.

Multiple Inserts Rows Performance​

These benchmarks show the performance of executing Multiple Row Inserts in batches of 1000 - measuring the comparative performance of each RDBMS in executing large SQL Insert statements:

PostgreSQL continues to shine here, significantly out performing other distributed RDBMS's for processing large, multi-row INSERT statements whilst SQL Server has a surprisingly poor showing on its native Windows/Intel platform - likely the result of a naive unoptimized implementation for these types of large SQL INSERT statements.

Single Insert Performance​

This benchmark measures the performance of multiple single inserts (i.e. when Bulk Insert is not available):

We hope these results have been informative and have highlighted opportunities for improvements in your own systems needing to perform large inserts or data imports.

Feel free to leave any feedback or suggest improvements or other environments to run these benchmarks on in the comments.