在SQL Server中插入一百万行的最快方法 [英] Fastest way to insert 1 million rows in SQL Server
问题描述
我正在编写一个存储过程，以将行插入表中.问题在于，在某些操作中，我们可能希望插入超过一百万行，并且希望使其速度更快.另一件事是，在其中一列中，它是 Nvarchar(MAX).我们可能希望在此列中平均输入1000个字符.
首先，我编写了一个prc来逐行插入.然后，我生成一些随机数据以用于插入 NVARCHAR(MAX)列，该字符串为1000个字符的字符串.然后使用循环调用prc插入行.性能非常差，如果我使用SQL Server登录要插入的数据库服务器，则需要48分钟.如果我使用C#连接到台式机中的服务器(这是我们通常想要做的)，则大约需要90分钟以上的时间.
然后，我将prc更改为采用表类型参数作为输入.我以某种方式准备了行，并将其放入表类型参数中，并通过以下命令进行插入:
INSERT INTO tableA SELECT * from @tableTypeParameterB 我尝试将批量大小设置为1000行和3000行(将@tableTypeParameterB中的1000-3000行放入一次).性能还是很差的.如果在SQL Server中运行一百万行，则大约需要3分钟；如果使用C#程序从桌面进行连接，则大约需要10分钟.
tableA 具有包含2列的聚集索引.
我的目标是使插入尽可能快(我的目标是在1分钟内).有什么方法可以优化它?
只需更新:
我尝试了以下某些人建议的大容量复制插入.我尝试使用SQLBULKCOPY一次插入1000行和10000行.插入100万行的性能仍然是10分钟(每行都有一个包含1000个字符的列).性能没有改善.还有其他建议吗?
根据评论要求进行的更新.
数据实际上来自UI.用户将更改用户界面以批量选择一百万行，并将一列从旧值更改为新值.该操作将在一个单独的过程中完成.但是，我们需要做的是使中间层服务从UI中获取旧值和新值，并将它们插入表中.旧值和新值最多可包含4000个字符，平均为1000个字符.我认为长字符串的旧值/新值会减慢速度，因为当我将测试数据的旧值/新值更改为20-50个字符并插入时非常快，无论使用SQLBulkCopy还是表类型变量
我认为您正在寻找的是 ADO.NET批处理操作选项，因此您可以将逻辑保留在C#应用程序中.本文也很完整
更新
是的，恐怕批量插入仅适用于导入的文件(来自数据库内部).
我有一个Java项目的经验，我们需要插入数百万行(数据来自应用程序外部).
数据库是Oracle，因此我们当然使用了Oracle的多行插入.事实证明，Java批处理更新比Oracle多值插入(所谓的批量更新")快了许多.
我的建议是:
I think what you are looking for is Bulk Insert if you prefer using SQL.
Or there is also the ADO.NET for Batch Operations option, so you keep the logic in your C# application. This article is also very complete.
Update
Yes I'm afraid bulk insert will only work with imported files (from within the database).
I have an experience in a Java project where we needed to insert millions of rows (data came from outside the application btw).
Database was Oracle, so of course we used the multi-line insert of Oracle. It turned out that the Java batch update was much faster than the multi-valued insert of Oracle (so called "bulk updates").
My suggestion is:
- Compare the performance between the multi-value insert of SQL Server code (then you can read from inside your database, a procedure if you like) with the ADO.NET Batch Insert.
If the data you are going to manipulate is coming from outside your application (if it is not already in the database), I would say just go for the ADO.NET Batch Inserts. I think that its your case.
Note: Keep in mind that batch inserts usually operate with the same query. That is what makes them so fast.
这篇关于在SQL Server中插入一百万行的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
