SqlBulkCopy *VERY* slow on Azure Sql and C#

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


Using SqlBulkCopy and FastMember, I am entering records into SQL.

Locally, it takes me roughly two seconds to input 100k records. This takes more than 10 minutes and times out when I run it on an Azure web app webjob and an Azure SQL database. Similar quantities of data are in the table, and the table definition is the same. Nothing is locked; it's simply sluggish. It takes more than 10 minutes to write to the Azure Sql database when I run it locally.

The call itself couldn't be more straightforward:

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

I've attempted to delete the transaction usingTransactionScope(Suppress) but it hasn't changed anything.

Could someone please assist me by either telling me what a dumb mistake I did or by offering me some advice on how to identify this? It's becoming quite annoying! I'm certain I've missed something important because of how much time has passed.

11/18/2017 12:42:31 PM

Popular Answer

Well. Even after I eliminated all the indices, the batch was still clocking out after 10 minutes. I did not use the outer ambient transaction scope and insteadTransactionScope.Suppress ) and all of a sudden, everything seem to be returning to "normal." It takes around 50 seconds to insert, and while it's running, it's coming closer to maxing out the DTU as opposed to the 20 percent it did before.

Although I still don't understand why it operates locally in 2s with the ambient transaction, I'll have to include this as an experience.

Thank you to everyone who responded; at the very least, you gave me some wonderful learning tips!

11/19/2017 2:18:54 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow