• Article

Optimizing Bulk Import Performance

This topic describes options for optimizing the bulk import of data into a table in Microsoft SQL Server by using a bcp command, BULK INSERT statement, or OPENROWSET(BULK...) function (Transact-SQL). To bulk import or export data as rapidly as possible, it is important to understand the factors that affect performance and the command qualifiers that are available to manage performance. Where possible, use a Transact-SQL statement to bulk import data into SQL Server because Transact-SQL is faster than bcp.

Note

For a comparison of these methods, see About Bulk Import and Bulk Export Operations.

How to best increase the performance of a particular bulk import operation is influenced by the following factors:

  • Whether the table has constraints or triggers, or both.

  • The recovery model used by the database.

    For more information, see Recovery Model Overview.

  • 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.

Important

In SQL Server 2005 and later versions, bulk-import optimizations are available when triggers are enabled. Row versioning is used for triggers and stores the row versions in the version store in tempdb. Before you can bulk import a large batch of data records using triggers, you may have to expand the size of tempdb to accommodate the impact of the triggers on the version store.

For information of how these factors affect bulk import scenarios, see Guidelines for Optimizing Bulk Import.

Methods for Optimizing Bulk Import

To speed up the bulk import of data, SQL Server provides you with the following methods:

  • Using minimal logging

    The simple recovery model minimally logs most bulk operations.

    For a database under the full recovery model, all row-insert operations that are performed during bulk import are fully logged in the transaction log. For large data imports, this can cause the transaction log to fill rapidly. For bulk-import operations, minimal logging is more efficient than full logging and reduces the possibility that a bulk-import operation will fill the log space. To minimally log a bulk-import operation on a database that normally uses the full recovery model, you can first switch the database to the bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

    Note

    Inserted rows are minimally logged if optimized bulk logging is applicable; otherwise, the inserted rows are fully logged in the transaction log. For information on when bulk-import operations are logged and how to perform minimally logged bulk-import operations, see Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import.

  • Importing data in parallel from multiple clients to a single table

    SQL Server allows data to be bulk imported into a single table from multiple clients in parallel. All three bulk import mechanisms support the parallel import of data. This can improve the performance of data import operations.

    For more information, see Importing Data in Parallel with Table Level Locking.

  • Using batches

    For information on the use of batches when importing data and for information about the command qualifiers for managing batches, see Managing Batches for Bulk Import.

    Note

    The OPENROWSET clause's BULK option does not support a controlling the batch size.

  • Disabling triggers

    Disabling triggers may improve performance.

    For more information on the impact of trigger execution on bulk-import operations and how to enable or disable triggers, see Controlling Trigger Execution When Bulk Importing Data.

  • Disabling constraints

    For information on the impact of constraint checking on bulk-import operations and how to enable or disable the CHECK and FOREIGN KEY constraints of a table, see Controlling Constraint Checking by Bulk Import Operations.

  • Ordering the data in a data file

    By default, a bulk-import operation assumes that a data file is unordered. If the table has a clustered index, the bcp utility, BULK INSERT statement, and OPENROWSET(BULK…) function (Transact-SQL) enable you to specify how data in the data file is sorted during a bulk-import operation. It is optional for data in the data file to be sorted in the same order as the table. However, you can improve performance of the bulk-import operation if you specify the same ordering for the data file as the table.

    For more information, see Controlling the Sort Order When Bulk Importing Data.

  • Controlling the locking behavior

    For information on how to specify the locking behavior during bulk-import operations, see Controlling Locking Behavior for Bulk Import.

  • Importing data in native format

    For more information, see Using Native Format to Import or Export Data and Using Unicode Native Format to Import or Export Data.