c#, EF &LINQ:在向SQL Server插入大(7Mb)记录时很慢
本文关键字:7Mb 记录 插入 Server EF LINQ SQL 在向 | 更新日期: 2023-09-27 18:02:17
这个问题有一个长版本,一个短版本。
简写:
为什么LINQ和EF在向远程SQL Server数据库插入单个大(7mb)记录时都如此缓慢?
这里是长版本(有一些关于解决方法的信息,可能对其他读者有用):
下面所有的示例代码都可以正常运行,但是由于我的用户在欧洲,而我们的数据中心在美国,所以速度非常慢。但如果我在美国的一台虚拟电脑上运行同样的代码,它马上就能运行。(不,遗憾的是,我的公司希望将所有数据保留在内部,所以我不能使用Azure,亚马逊云服务等)
我的很多公司应用程序都涉及到从Excel读取/写入数据到SQL Server,通常,我们想要在SQL Server表中保存Excel文件的原始副本。
这非常简单,只需从本地文件中读取原始数据,并将其保存到记录中。
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秒。
从SQL Server导出blob数据到外部文件
当然,在相反的方向上也是如此。
首先,我编写了一些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秒,即使我们的数据中心在6000英里之外。
所以,这个故事的寓意是:当插入非常大的数据库记录时,避免使用常规的SubmitChanges()
命令,而坚持使用bulk-insert。
您可以尝试使用profiler来查看实体框架正在做什么插入。例如,如果它从表中选择数据,则可能需要很长时间才能通过网络返回数据,而您在本地可能没有注意到这一点。
我发现从c#加载大量数据(记录计数和记录大小)到sql server的最佳方法是使用SqlBulkCopy类。即使您只插入了一条记录,您仍然可以从这个更改中获益。
要使用大容量复制,只需创建一个与表结构匹配的数据表。然后像这样调用代码。
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = "External_File";
bulkCopy.WriteToServer(dataTable);
}