SqlBulkCopy *VERY* slow on Azure Sql and C#

azure azure-sql-database c# sqlbulkcopy sql-server

Question

I am inserting records into sql using SqlBulkCopy and FastMember

Locally I can insert 100k records in about 2 seconds. When I run this on an Azure web app webjob and an Azure Sql Database it's taking more than 10 minutes and timing out the transaction. The table definition is the same, similar amounts of data in the table etc. There are no locks, it's just slow. When I run it locally and try to write to the Azure Sql database it takes > 10 minutes too.

The actual call is as simple as it could be:

using(var bulkCopy = new SqlBulkCopy(connection){DestinationTableName="Table")
using(var reader = ObjectReader.Create(entities, columnList)
{
   await bulkCopy.WriteToServerAsync(reader).ConfigureAwait(false);
}

I've tried removing the transaction using TransactionScope(Suppress) but it's made no difference.

Could anyone help in either letting me know what stupid mistake I've made or giving me some hints as to how to diagnose this? It's getting really frustrating! The difference in time is just so big that I'm sure that I've missed something fundamental here.

Popular Answer

Well. I removed all the indexes, and it made some difference, but the batch was still timing out at 10 minutes. I removed the outer ambient transaction scope (rather than using TransactionScope.Suppress) and all of a sudden the times are looking 'normal' again. It's taking about 50 seconds to insert, and it's getting closer to maxing the DTU while it's running, whereas before it only got to about 20%.

I still have no idea why it works locally in 2s with the ambient transaction, but it'll have to be one to chalk up to experience

thanks all who answered - and at least pointed me in a good direction to learn!




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why