C#、EF和LINQ :缓慢地将大型(7Mb)记录插入Server

Question

这个问题有一个很长的版本，也有一个简短的版本。

短版：

为什么LINQ和EF在将单个大记录(7 Mb)插入远程Server数据库时速度如此慢？

和下面的长版本(包含一些关于解决方法的信息，可能对其他读者有用)：

下面的所有示例代码都运行良好，但由于我的用户在欧洲，而我们的数据中心设在美国，这是非常缓慢的。但是如果我在美国的虚拟PC上运行相同的代码，它就会立即运行。(不，遗憾的是，我的公司想把所有的数据保存在内部，所以我不能使用Azure，Amazon服务等等)

我的很多公司应用程序都涉及到将Excel中的数据读写到Server中，而且我们通常希望将Excel文件的原始副本保存在Server表中。

这样做非常简单，只需从本地文件读取原始数据，并将其保存到记录中。

private int SaveFileToSQLServer(string filename)
{
    //  Read in an Excel file, and store it in a SQL Server [External_File] record.
    //
    //  Returns the ID of the [External_File] record which was added.
    //

    DateTime lastModifed = System.IO.File.GetLastWriteTime(filename);
    byte[] fileData = File.ReadAllBytes(filename);

    //  Create a new SQL Server database record, containing our file's raw data 
    //  (Note: the table has an IDENTITY Primary-Key, so will generate a ExtFile_ID for us.)
    External_File newFile = new External_File()
    {
        ExtFile_Filename = System.IO.Path.GetFileName(filename),
        ExtFile_Data = fileData,
        ExtFile_Last_Modified = lastModifed,
        Update_By = "mike",
        Update_Time = DateTime.UtcNow
    };
    dc.External_Files.InsertOnSubmit(newFile);
    dc.SubmitChanges(); 

    return newFile.ExtFile_ID;
}

是的，没有惊喜，而且效果很好。

但是，我注意到的是，对于大型Excel文件(7-8Mb)，这段代码要插入一个(大！)记录需要40-50秒才能完成。我把它放在后台线程中，所有这些都工作得很好，但是，当然，如果用户退出我的应用程序，这个进程将被关闭，这将导致问题。

作为一个测试，我尝试用代码替换这个函数来完成以下工作：

• 将文件复制到Server计算机上的共享目录中
• 调用存储过程将原始数据(blob)读入同一个表中。

使用这种方法，整个过程只需3-4秒。

如果您感兴趣，下面是我用来将文件(必须存储在Server机器本身的文件夹中)上载到数据库记录中的存储过程：

CREATE PROCEDURE [dbo].[UploadFileToDatabase]
    @LocalFilename nvarchar(400)
AS
BEGIN
    --  By far, the quickest way to do this is to copy the file onto the SQL Server machine, then call this stored
    --  procedure to read the raw data into a [External_File] record, and link it to the Pricing Account record.
    --
    --      EXEC [dbo].[UploadPricingToolFile] 'D:\ImportData\SomeExcelFile.xlsm'
    -- 
    --  Returns: -1 if something went wrong  (eg file didn't exist) or the ID of our new [External_File] record
    --
    --  Note that the INSERT will go wrong, if the user doesn't have "bulkadmin" rights.
    --      "You do not have permission to use the bulk load statement."
    --  EXEC master..sp_addsrvrolemember @loginame = N'GPP_SRV', @rolename = N'bulkadmin'
    --
    SET NOCOUNT ON;

    DECLARE 
        @filename nvarchar(300),        --  eg "SomeFilename.xlsx"  (without the path)
        @SQL nvarchar(2000),
        @New_ExtFile_ID int

    --  Extract (just) the filename from our Path+Filename parameter
    SET @filename = RIGHT(@LocalFilename,charindex('\',reverse(@LocalFilename))-1)

    SET @SQL = 'INSERT INTO [External_File]  ([ExtFile_Filename], [ExtFile_Data]) '
    SET @SQL = @SQL + 'SELECT ''' + @Filename + ''', * 
    SET @SQL = @SQL + ' FROM OPENROWSET(BULK ''' + @LocalFilename +''', SINGLE_BLOB) rs'

    PRINT convert(nvarchar, GetDate(), 108) + ' Running: ' + @SQL
    BEGIN TRY
        EXEC (@SQL)
        SELECT @New_ExtFile_ID = @@IDENTITY
    END TRY
    BEGIN CATCH
        PRINT convert(nvarchar, GetDate(), 108) + ' An exception occurred.'
        SELECT -1
        RETURN
    END CATCH

    PRINT convert(nvarchar, GetDate(), 108) + ' Finished.'

    --  Return the ID of our new [External_File] record
    SELECT @New_ExtFile_ID
END

此代码的关键是构建如下SQL命令：

INSERT INTO [External_File]  ([ExtFile_Filename], [ExtFile_Data])
SELECT 'SomeFilename.xlsm', * FROM OPENROWSET(BULK N'D:\ImportData\SomeExcelFile.xlsm', SINGLE_BLOB) rs

。。而且，由于要上载的数据库和文件都在同一台机器上，这几乎立即运行。

如前所述，总的来说，将文件复制到Server机器上的文件夹需要3-4秒，然后运行此存储过程，相比之下，使用LINQ或EF使用C#代码执行相同的操作需要40-50秒。

将blob数据从Server导出到外部文件

当然，在相反的方向也是如此。

首先，我编写了一些C#/LINQ代码来加载这个代码(7Mb !)数据库记录并将其二进制数据写入原始文件。这需要30-40秒的时间才能运行。

但是，如果我首先将Server数据导出到一个文件(保存在Server计算机上)。

EXEC master..xp_cmdshell 'BCP "select ef.ExtFile_Data FROM [External_File] ef where ExtFile_ID = 585" queryout "D:\ImportData\SomeExcelFile.xslx" -T -N'

然后，...and将文件从Server文件夹复制到用户的文件夹，然后再一次在几秒钟内运行。

这就是我的问题:为什么LINQ和EF在向数据库中插入一个大记录方面都那么糟糕？

我认为延迟(在欧洲这里和美国的数据中心之间的距离)是延迟的一个主要原因，但奇怪的是，一个沼泽标准的文件复制速度会快得多。

我漏掉了什么吗？

显然，我已经发现了这些问题的解决方法，但是它们涉及到在Server机器上为我们的Server机器和共享文件夹添加一些额外的权限，我们的DBA真的不喜欢为“xp_cmdshell”这样的东西授予权限.

几个月后的.

本周，我又遇到了同样的问题，并尝试了Kevin的建议，即使用Bulk-Insert将大型(6Mb)记录插入到Server中。

使用大容量插入，插入6Mb记录花费了大约90秒的时间，尽管我们的数据中心在6000英里之外。

因此，故事的寓意是:当插入非常大的数据库记录时，避免使用常规的SubmitChanges()命令，并坚持使用大容量插入。

Answer 1

您可以尝试使用分析器来查看实体框架对insert所做的操作。例如，如果它从表中选择数据，则可能需要很长时间才能通过连线返回数据，您可能不会注意到这一点。

我发现从c#向sql server加载大量数据(包括记录计数和记录大小)的最佳方法是使用SqlBulkCopy类。即使您只插入了1条记录，您仍然可以从此更改中获益。

若要使用大容量复制，只需创建与表结构匹配的datatable即可。然后像这样调用代码。

using (SqlConnection destinationConnection = new    SqlConnection(connectionString))
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
    bulkCopy.DestinationTableName = "External_File";
    bulkCopy.WriteToServer(dataTable);
}