Microsoft® SQL Server™ 2000 Unleashed, Second Edition

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