Search code examples
sql-serverrestunique-constraint

Making .NET REST API bulk upload faster without incurring SQL violation unique key

I have a C# .NET 6 Web API using SQL Server database. I have an API to bulk upload thousands of rows at a time. If I write the service layer to insert each row one at a time, it works, but it is slow. It would be 3x faster if I call db.SaveChanges() only once per 200 rows. (I tried it).

The problem is that when one record fails (for example Violation of UNIQUE KEY) then all 200 records fail to insert.

I tried to check for key violations in advance (against the database and other records in the set of 200) and that works on localhost... but for some weird reason fails when I deploy it to Azure.

Is there any way to do this? Do I have to choose between "fast" or "reliable"?

Solution

  • After a failed SaveChanges catch the DbUpdateException, and you can detach or correct the failed entities and try again. SaveChanges() uses a transaction, so on failure none of the changes have been saved.

    Note you may have to try multiple times, as typically only the first failure is returned to the client.