.NET slow insert in remote SQL Server database

Question

I developed a client .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. Insertions are very fast (over 500000 records in about 2 mins).

Now I'm trying to use a remote database on a hosted server and I notice that insertions are very very slow (less than 500 records in about 2 mins). This means 1000 times slower than locally.

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

Is the problem on my client application?

Here 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 to use Linq-to-SQL instead of EF6 but the result is the same. Maybe is not a specific EF6 problem.

Some infos about the remote system:

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

Thanks in advance.

UPDATE AFTER SOME TESTS WITH BULKINSERT

Ok here the results of my first tests with BulkInsert:

• 100 records -> EF6 AddRange: 9 sec. / EF6 BulkInsert: 1 sec.
• 1000 records -> EF6 AddRange: 1:27 min. / EF6 BulkInsert: 1 sec. (wow!)
• 10000 records -> EF6 AddRange: 14:39 min. / EF6 BulkInsert: 4 sec. (wooooow!)

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

Answer 1

Looks like most time is spend on the network waiting for a round-trip to complete. EF cannot be made to batch inserts (yet). So you cannot use EF for inserts here.

Investigate the typical solutions to this problem (TVPs and SqlBulkCopy).

---

The dispose pattern you are using is not a good choice. Just wrap dc in ´using` and delete all exception handling.

Answer 2

As suggested, SqlBulkCopy is your best bet, however there is an Interesting Nuget Package which does BulkInsert for Entity Framework:

https://efbulkinsert.codeplex.com/