Fast, Fast-logged, and Slow bcp

Whether to use fast, fast-logged, or slow bcp depends on your situation.

The existence of indexes on tables can affect transfer speed, depending on certain attributes. Unless you explicitly specify fast-logged bcp on tables with indexes, bcp automatically uses slow mode, which logs data inserts in the transaction log. These logged inserts can cause the transaction log to become very large.

To control this data excess and ensure that the database is fully recoverable in the event of a failure, back up the log with dump transaction.

By default, the select into/bulkcopy/pllsort option is false (disabled) in newly created databases. When this option is disabled, bcp automatically uses slow mode. Fast and fast-logged bcp both require that select into/bulkcopy/pllsort option is set to true. To change the default setting for future databases, turn this option on in the model database.

Note: You need not set the select into/bulkcopy/pllsort option to true to copy out data from, or to copy in data to a table that has indexes. Slow bcp always copies tables with indexes and logs all inserts.

The differences between fast, fast-logged, and slow bcp are:

Fast bcp:
Lock Scheme Index Type select into Recovery

APL Non-unique nonclustered On No

DOL Non-unique nonclustered On No

DOL Non-unique clustered On No
Fast-logged bcp (in Adaptive Server 15.7 ESD #2):
Lock Scheme Index Type select into Recovery

APL Non-unique nonclustered On Yes

DOL Non-unique nonclustered On Yes

DOL Non-unique clustered On Yes
Slow bcp:
Lock Scheme Index Type select into/bulkcopy Recovery

APL Unique On/off Yes

DOL Unique On/off Yes

APL Non-unique Off Yes

DOL Non-unique Off Yes

Lock Scheme	Index Type	select into	Recovery
APL	Non-unique nonclustered	On	No
DOL	Non-unique nonclustered	On	No
DOL	Non-unique clustered	On	No

Lock Scheme	Index Type	select into	Recovery
APL	Non-unique nonclustered	On	Yes
DOL	Non-unique nonclustered	On	Yes
DOL	Non-unique clustered	On	Yes

Lock Scheme	Index Type	select into/bulkcopy	Recovery
APL	Unique	On/off	Yes
DOL	Unique	On/off	Yes
APL	Non-unique	Off	Yes
DOL	Non-unique	Off	Yes

While the select into/bulkcopy/pllsort option is on, you cannot dump the transaction log. Issuing dump transaction produces an error message instructing you to use dump database instead.

Warning! Be certain that you dump your database before you turn off the select into/bulkcopy/pllsort flag. If you have inserted unlogged data into your database, and you then perform a dump transaction before performing a dump database, you will not be able to recover your data.

Adaptive Server prohibits dump transaction after running fast bcp. Instead, use dump database. Because slow bcp is a minimally logged operation, Adaptive Server allows you to issue dump transaction after running slow bcp whether select into/bulkcopy/pllsort is set to true or false in the database.

Slow bcp
In certain situations, use slow bcp.
Fast bcp
Adaptive Server uses fast bcp in certain situations.
Fast-logged bcp
Use fast-logged bcp on any table the includes indexes and triggers (bcp does not fire any triggers in the target table). Fast-logged bcp logs inserts to tables.
Copying Tables with Indexes
The bcp utility is optimized to load data into tables that do not have indexes associated with them.
Space Requirements for Copying
If you are copying a very large number of rows, you must have 1.2 times the amount of space needed for the data and enough space for the server to reconstruct a clustered index.

Parent topic: Improve bcp Performance