gpt4 book ai didi

C#、EF 和 LINQ : slow at inserting large (7Mb) records into SQL Server

转载 作者:可可西里 更新时间:2023-11-01 07:47:44 26 4
gpt4 key购买 nike

这个问题有一个长版和一个短版。

短版:

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

这是长版 (提供一些有关变通方法的信息,可能对其他读者有用):

以下所有示例代码都运行正常,但由于我的用户在欧洲,而我们的数据中心位于美国,所以运行速度非常慢。但是如果我在美国的 Virtual PC 上运行相同的代码,它会立即运行。 (不,遗憾的是我的公司希望将所有数据保留在内部,所以我不能使用 Azure、亚马逊云服务等)

我的很多公司应用程序都涉及从 Excel 读取/写入数据到 SQL Server,而且通常,我们希望将 Excel 文件的原始副本保存在 SQL 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 秒才能运行。我把它放在一个后台线程中,一切都很好,但是,当然,如果用户退出我的应用程序,这个进程就会被终止,这会导致问题。

作为测试,我尝试用代码替换此函数来执行此操作:
  • 将文件复制到 SQL Server 计算机上的共享目录
  • 调用存储过程将原始数据 (blob) 读入同一个表

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

    如果您有兴趣,这里是我用来将文件(必须存储在 SQL 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

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

    正如我所说,总体而言,将文件复制到 SQL Server 计算机上的文件夹并运行此存储过程需要 3-4 秒,而使用 C# 代码和 LINQ 或 EF 执行相同操作需要 40-50 秒。

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

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

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

    但是如果我首先将 SQL Server 数据导出到一个文件(保存在 SQL 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'

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

    这是我的问题:为什么 LINQ 和 EF 在将单个大记录插入数据库方面都如此糟糕?

    我认为延迟(我们在欧洲的距离和我们在美国的数据中心之间的距离)是延迟的主要原因,但奇怪的是,沼泽标准的文件副本可以快得多。

    我错过了什么吗?

    显然,我已经找到了解决这些问题的方法,但它们涉及向我们的 SQL Server 机器和 SQL Server 机器上的共享文件夹添加一些额外的权限,而我们的 DBA 真的不喜欢为“ xp_cmdshell”之类的东西授予权限。 ..

    几个月后...

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

    使用批量插入,插入 6Mb 记录需要大约 90 秒,即使我们的数据中心在 6,000 英里之外。

    所以,故事的寓意是:在插入非常大的数据库记录时,避免使用常规 SubmitChanges()命令,并坚持使用批量插入。

    最佳答案

    您可以尝试使用探查器来查看 Entity Framework 正在对插入进行什么操作。例如,如果它从您的表中选择数据,则通过网络返回数据可能需要很长时间,而您可能不会在本地注意到这一点。

    我发现将大量数据(记录计数和记录大小)从 c# 加载到 sql server 的最佳方法是使用 SqlBulkCopy类(class)。即使您只插入了 1 条记录,您仍然可以从这一更改中受益。

    要使用批量复制,只需创建一个与表结构匹配的数据表。然后像这样调用代码。

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

    关于C#、EF 和 LINQ : slow at inserting large (7Mb) records into SQL Server,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31342732/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com