Why is BCP so fast?

So BCP for inserting data into 开发者_如何学JAVAa SQL Server DB is very very fast. What is is doing that makes it so fast?

In SQL Server, BCP input is logged very differently than traditional insert statements. How SQL decides to handle things depends on a number of factors and some are things most developers never even consider like what recovery model the database is set to use.

bcp uses the same facility as BULK INSERT and the SqlBulkCopy classes.

More details here http://msdn.microsoft.com/en-us/library/ms188365.aspx

The bottom line is this, these bulk operations log less data than normal operations and have the ability to instruct SQL Server to ignore its traditional checks and balances on the data coming in. All those things together serve to make it faster.

It cheats.

It has intimate knowledge of the internals and is able to map your input data more directly to those internals. It can skip other heavyweight operations (like parsing, optimization, transactions, logging, deferring indexes, isolation). It can make assumptions that apply to every row of data that a normal insert sql statement can not.

Basically, it's able to skip a bulk of the functionality that makes a database a database, and then clean up after itself en masse at the end.

The main difference I know between bcp and a normal insert is that bcp doesn't need to keep a separate transaction log entry for each individual transaction.

The speed is because they use of BCP API of the SQL Server Native Client ODBC driver. According to Microsoft:

http://technet.microsoft.com/en-us/library/aa337544.aspx

The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API...

Bulk Copy Functions reference:

http://technet.microsoft.com/en-us/library/ms130922.aspx