Kudos to Pablo Castro, the Program Manager for the ADO.NET Team at Microsoft, for explaining why SqlBulkCopy in ADO.NET 2.0 is faster than inserting records into a table.
"There are a number of reasons why bulk-copy is faster. Here is a summary:
- No per-row statement execution. When you do multiple inserts without bulk-copy, each insert is a statement in itself (regardless of whether it's batched together with other statements). With bulk-copy, we don't incur the cost of executing a statement for each row, the whole copy operation is a single thing.
- No multiple network round-trips. Once the bulk-insert operation is setup, we send rows from the client to the server continously, without going back-and-forth over the wire.
- Server storage engine also can greatly optimize how rows are inserted when performing a bulk-copy operation. How much can be optimized depends a lot on the recovery model the tarder database is set to; in "simple" and "bulk logged" the overhead of logging is greatly reduced during bulk-copy operations, helping a lot with performance.
Now, as to "how" that happens, it's hard to describe without going down to the details of the SQL Server client-server protocol. The short story is that we setup a BCP operation by sending a special statement to the server that includes metadata about the row-set we're about to set; that switches the session to bulk-copy mode, at which point the client and start sending the row stream to the server one after the other; as rows come the server does minimal processing in the upper layers, rows go straight to the storage layer. Once the client is done it sends a "done" marker and the operation is completed in the server. This operation can happen in "batches" of rows or all at once." - Pablo Castro
You can read the entire forum thread here as he goes into a bit more detail.
Drinking: Jasmine Pearls Green Tea