.NET slow insertion in remote SQL Server database

Question

I developed the .NET WinForms application. This application is used to populate a database with thousands of records. For the data layer, I used EF6.

When I work and run the application locally, every thing works as expected. The inserts are very fast (over 500,000 records in 2 minutes).

Now I'm trying to use a remote database on a hosted server, and I notice that the inserts are very slow (less than 500 records in 2 minutes). This means that it is 1000 times slower than locally.

If I try to insert 500 records in a remote database using SQL Server Management Studio, the operation will be completed in less than 1 second.

Is there a problem in my client application?

Here's the insert function:

public void SetDemoDimCustomer() { DWContext dc = null; try { dc = DWContext.Create(SqlServerInstance, DbName); dc.Configuration.AutoDetectChangesEnabled = false; dc.Database.ExecuteSqlCommand("DELETE FROM DimCustomer"); dc.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('DimCustomer', RESEED, 0)"); DimCustomer objCustomer; List<DimCustomer> lstDemoCustomers = new List<DimCustomer>(); int length = 100; for (int i = 0; i < length; i++) { objCustomer = new DimCustomer(); objCustomer.Name = "Customer " + (i + 1); objCustomer.CustomerBKey = (i + 1).ToString(); lstDemoCustomers.Add(objCustomer); } dc.DimCustomer.AddRange(lstDemoCustomers); dc.SaveChanges(); } catch (Exception) { throw; } finally { if (dc != null) { dc.Dispose(); } } } 

I tried using Linq-to-SQL instead of EF6, but the result is the same. Perhaps this is not a specific problem with EF6.

Some information about the remote system:

• OS: Windows Server 2012
• RDBMS: SQL Server 2014 Express

Thanks in advance.

UPDATE AFTER SOME TESTS WITH BULKINSERT

Well here are the results of my first tests with BulkInsert:

• 100 entries → EF6 AddRange: 9 sec. / EF 6 BulkInsert: 1 s.
• 1000 entries → EF6 AddRange: 1:27 min. / EF 6 BulkInsert: 1 sec. (Wow!)
• 10,000 entries → EF6 AddRange: 14:39 min. / EF 6 BulkInsert: 4 sec. (Wooooow!)

Now, of course, the EF6 BulkInsert package is part of my project.

Answer 1

It seems like most of the time is spent on the network, waiting for the round-trip completion. EF cannot be inserted into batch inserts (yet). So you cannot use EF for inserts here.

Learn typical solutions to this problem (TVPs and SqlBulkCopy).

---

The placement template you are using is not a good choice. Just wrap dc in'using` and remove all exception handling.

Answer 2

As suggested, SqlBulkCopy is your best bet, however there is an interesting Nuget package that BulkInsert for Entity Framework:

https://efbulkinsert.codeplex.com/