.NET缓慢插入远程SQL服务器数据库

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.

我开发了一个客户端。net WinForms应用程序。此应用程序用于用数千条记录填充数据库。对于数据层，我使用了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).

当我在本地运行应用程序时，所有的东西都可以正常工作。插入非常快(2分钟内超过500000条记录)。

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.

现在我尝试在托管服务器上使用远程数据库，我注意到插入非常缓慢(大约2分钟不到500条记录)。这意味着比本地慢1000倍。

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.

如果我尝试使用SQL Server Management Studio在远程数据库中插入500条记录，操作将在不到1秒的时间内完成。

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.

我尝试使用Linq-to-SQL而不是EF6，但是结果是一样的。也许不是一个具体的EF6问题。

Some infos about the remote system:

关于远程系统的一些信息:

OS: Windows Server 2012
操作系统:Windows Server 2012
RDBMS: SQL Server 2014 Express
RDBMS: SQL Server 2014 Express。

Thanks in advance.

提前谢谢。

UPDATE AFTER SOME TESTS WITH BULKINSERT

在使用BULKINSERT进行一些测试后更新

Ok here the results of my first tests with BulkInsert:

这里是我用BulkInsert做的第一次测试的结果:

100 records -> EF6 AddRange: 9 sec. / EF6 BulkInsert: 1 sec.
100项记录-> EF6 AddRange: 9秒/ EF6 BulkInsert: 1秒。
1000 records -> EF6 AddRange: 1:27 min. / EF6 BulkInsert: 1 sec. (wow!)
> EF6 AddRange: 1:27分钟/ EF6 BulkInsert: 1秒(哇!)
10000 records -> EF6 AddRange: 14:39 min. / EF6 BulkInsert: 4 sec. (wooooow!)
10000record -> EF6 AddRange: 14:39分钟/ EF6 BulkInsert: 4秒(哇!)

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

当然，EF6 BulkInsert包是我项目的一部分。

2 个解决方案

#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.

看起来大部分时间都花在网络上，等待往返完成。EF不能批量插入(目前)。所以不能在这里使用EF进行插入。

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

研究这个问题的典型解决方案(TVPs和SqlBulkCopy)。

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

您正在使用的dispose模式不是一个好的选择。只是裹直流´使用和删除所有异常处理。

#2

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

正如建议的，SqlBulkCopy是您最好的选择，但是有一个有趣的Nuget包，它为实体框架做了BulkInsert:

https://efbulkinsert.codeplex.com/

#1