Bulk Insert or BCP in Azure SQL Server

Question

I am trying to load data into Azure SQL server using BULK Insert and BCP. I am able to load data into my table without any issues, but load performance is the issue in my case. BCP is faster in most cases then BULK Insert.

BCP

Loading data from UNIX to database

BULK INSERT

Loading data into Azure SQL Server via BLOB storage

I would like to know to how to improve the BULK Insert performance other than adding TABLOCK and drop/recreate index. Drop and recreate index is not possible due to incremental loads.

What are all the parameters to tweak for improved BULK INSERT performance?

Answer 1

Batching calls to a remote service is a well-known strategy for increasing performance and scalability. There are fixed processing costs to any interactions with a remote service, such as serialization, network transfer, and deserialization. Packaging many separate transactions into a single batch minimizes these costs.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-use-batching-to-improve-performance#why-is-batching-important-for-sql-database

Answer 2

1. BCP is an utility program: bcp.exe.
2. BULK INSERT is a Transact-SQL statement.

bcp.exe uses BULK INSERT to do its job.

BCP and BULK INSERT are actually different implementations. Logically they are equivalent, or at least supposed to be. BCP has less parsing efforts and cost than BULK INSERT. Both are almost same and give almost same performance.

If you want test or compare them performance, they must in the same system and do the same operation: loading data file into Azure SQL Server via BLOB storage or load data file into Azure SQL server. Otherwise, you may get the incorrect result.

Azure SQL Server doesn't specifically how to improve BULK INSERT performance. But I think if we follow the document BULK INSERT (Transact-SQL) does, there may not have performance issues.

Hope this helps.