批量插入在内部如何工作？ [英] How does BULK INSERT work internally?

问题描述

有人可以解释一下BULK INSERT在内部如何工作，为什么它比正常的INSERT操作要快得多？

Could someone please explain how does BULK INSERT internally work and why is it much faster than the normal INSERT operations ?

请注意，
Shishir。

Regards, Shishir.

推荐答案

BULK INSERT与SQL Server的数据库引擎一起在进程内运行，因此避免了通过客户端API的网络层传递数据-

BULK INSERT runs in-process with the database engine of SQL Server and thus avoids passing data through the network layer of the Client API - this makes it faster than BCP and DTS / SSIS.

另外，使用BULK INSERT，您可以指定数据的ORDER BY，如果与表的PK，然后锁定发生在PAGE级别。对事务日志的写入也发生在页面级别而不是行级别。

Also, with BULK INSERT, you can specify the ORDER BY of the data, and if this is the same as the PK of the table, then the locking occurs at a PAGE level. Writes to the transaction logs happen at a page level rather than a row level as well.

对于常规的INSERT，锁定和事务日志写入处于行级别。这使得BULK INSERT比INSERT语句快。

In the case of regular INSERT, the locking and the Transaction log writes are at a row level. That makes BULK INSERT faster than an INSERT statement.

这篇关于批量插入在内部如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！