Improving Load Performance

Here are the performance guidelines for BCP operation:

• You can improve load performance by two or more times by dropping indexes from the target table.

• If you have clean, verified data, you should determine the tradeoffs between ignoring check constraints using the BCP hint, and allowing the check to take place as part of the load operation.

• If you have business reasons that override the performance slowdown of firing insert triggers, this option can be used. But they better be good reasons.

• When you're importing/exporting from one SQL Server to another, use native mode; it is a bit faster than the other modes.

• If you have a recurring load, take the time to determine the best batch size for it. Otherwise, start with a batch size value on the order of a quarter to a half of the number of rows in the file, unless the number of rows is small (less than a few thousand).

• Consider the possibility of performing parallel data loads if you are loading large data files.

• Specify a large batch size for the ROWS_PER_BATCH hint. Ideally, the batch size should represent the total size of the file.

• Create ordered data files that match the clustered index, and utilize the ORDER hint.

• Lock the table to allow uninterrupted access during parallel loads.

• Perform non-logged operations whenever possible.

• Microsoft recommends that you use the BULK INSERT statement instead of the BCP utility when you want to bulk-copy data into SQL Server. The BULK INSERT statement is faster than BCP.