为什么批量导入比一堆INSERT更快？ [英] Why Bulk Import is faster than bunch of INSERTs?

问题描述

我正在写有关将数据从文件导入SQL Server表的方法的毕业论文。我已经创建了自己的程序，现在将其与一些标准方法进行比较，例如bcp，BULK INSERT，INSERT ... SELECT * FROM OPENROWSET（BULK ...）等。我的程序从源文件中逐行读取，解析它们并使用普通INSERT逐一导入它们。该文件包含100万行，每行4列。现在我遇到的情况是我的程序需要160秒，而标准方法需要5-10秒。他们使用特殊手段还是什么？您能解释一下还是给我一些有用的链接或其他东西？

解决方案

BULK INSERT可以是最少记录的操作（取决于各种
参数，例如索引，表上的约束，
数据库的恢复模型等）。最少记录的操作仅记录分配
和释放。在批量插入的情况下，仅会记录
范围分配，而不是实际插入的数据。与INSERT相比，这将提供
更好的性能。

比较批量插入与插入

实际的优点是减少事务日志中记录的数据量。

如果是批量记录或简单恢复模型

优化大容量导入性能

您还应该考虑阅读以下答案：从表中插入*从表中插入*与批量插入

顺便说一句，有一些因素会影响批量插入的性能：

表是否具有约束或触发器，或者

数据库使用的恢复模型。

复制数据的表是否为空

表是否具有索引。

是否指定了TABLOCK。

是从单个客户端复制数据还是从多个客户端并行复制
。

数据是否要可以在运行SQL
服务器的两台计算机之间复制。

I'm writing my graduate work about methods of importing data from a file to SQL Server table. I have created my own program and now I'm comparing it with some standard methods such as bcp, BULK INSERT, INSERT ... SELECT * FROM OPENROWSET(BULK...) etc. My program reads in lines from a source file, parses them and imports them one by one using ordinary INSERTs. The file contains 1 million lines with 4 columns each. And now I have the situation that my program takes 160 seconds while the standard methods take 5-10 seconds.

So the question is why are BULK operations faster? Do they use special means or something? Can you please explain it or give me some useful links or something?

解决方案

BULK INSERT can be a minimally logged operation (depending on various parameters like indexes, constraints on the tables, recovery model of the database etc). Minimally logged operations only log allocations and deallocations. In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT.

Compare Bulk Insert vs Insert

The actual advantage, is to reduce the amount of data being logged in the transaction log.
In case of BULK LOGGED or SIMPLE recovery model the advantage is significant.

Optimizing BULK Import Performance

You should also consider reading this answer : Insert into table select * from table vs bulk insert

By the way, there are factors that will influence the BULK INSERT performance :

Whether the table has constraints or triggers, or both.

The recovery model used by the database.

Whether the table into which data is copied is empty.

Whether the table has indexes.

Whether TABLOCK is being specified.

Whether the data is being copied from a single client or copied in parallel from multiple clients.

Whether the data is to be copied between two computers on which SQL Server is running.

这篇关于为什么批量导入比一堆INSERT更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！