Most performant way to insert thousands of rows of data into Azure SQL DB?-CodePudding

I'm working on an importer that generates data which needs to go into an Azure SQL DB (configured as serverless with 4vCores).

The objects I insert have only a handful of columns

Id : INT
Type : NVARCHAR
Uid : NVARCHAR
Json : NVARCHAR

The Json data is about 1.5kb in size on average. I'm importing around 3 million rows per run.

My current approach is to use one transaction per 2000 rows to be inserted (code excerpt below):

using var transaction = sqlConnection.BeginTransaction (System.Data.IsolationLevel.ReadUncommitted);

cmd = new SqlCommand (insertNodeQuery, sqlConnection) {
    CommandTimeout = 600
};

cmd.Transaction = transaction;
cmd.Parameters.AddWithValue ("@Type", node.Type);
[...]

var insertTask = cmd.ExecuteNonQueryAsync ();
tasks.Add (insertTask);

Then I use Task.WhenAll(tasks) to wait for the transaction to be completed.

On my PC, this gives me 20 inserts per second against (localdb). However, against Azure DB I'm only seeing around 5 inserts per second. I understand, there's network latency involved but even when running the code in an Azure Data Center (AKS) in the same region I'm not getting near the speed of the local DB.

This makes me wonder if there's a better way of running the import? Would SqlBulkCopy be an option, given I'm sending generated in-memory data to the DB?

How else can I optimize the performance?

CodePudding user response：

How else can I optimize the performance?

SqlBulkCopy is the best. You can load a DataTable to load with in-memory data, or use an adapter like this to convert a collection of in-memory objects to an IDataReader for use with SqlBulkCopy.

You can also send each batch as JSON doc as a parameter to a SQL query, where you read it with OPENJSON.

Both of these should be faster than single-row inserts.

Client-side loading methods in (rough) order of slowest to fastest are:

single-row inserts, no transaction
single-row inserts, with transaction
single-row inserts with TSQL batching
single-row inserts with TDS batching (SqlDataAdapter)
bulk insert with XML or JSON
bulk insert with Table-valued Parameters
bulk insert with SqlBulkCopy