What are the Bulk Import Optimizations?

Bulk import provides an optimized insert path that minimizes this overhead with following optimizations

· Minimal Logging: under this logging mode, individual rows are not logged and only the changes to page allocation structures are logged. This reduces the amount of logging significantly.

· BU Locking: a special mode table level locking only available for bulk import path. Using this locking mode, each bulk import thread acquires a table level BU lock while still allowing concurrent bulk import threads (i.e. parallel bulk import) into the same target table.

· Batchsize: provides a way to break the bulk import into smaller transactions by committing the transaction after ‘batchsize’ worth of rows have been successfully imported.

· Option to enable/disable triggers and constraints

· Option to leverage the sort order in the input set. So for example if you are bulk importing into a table with a cluseterd index and your input data is already sorted on clustered key columns, the sort operation can be skiped.

· LOB orphan optimization which eliminates the redundant copy of the LOB data (e.g. text or image). For normal insert, the LOB data is first copied into tempdb, the data row is then constructed and finally inserted which involves copying the LOB data from tempdb into the filegroup associated with the target table. However, for optimized bulk import path, the LOB data is directly copied into its final destination there by saving extra copy for each LOB value.

Comments

• Anonymous
  February 05, 2008
  The big news of the week is Windows Server 2008 RTM'ing. Read more here. The storage engine team has begin a series on bulk insert. It is definitely recommended reading. Part 1, Part 2, Part 3. The S ...