The existence of indexes on tables affects transfer speed. 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, you can 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.
You need not I aset 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.
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, you must 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.
Adaptive Server uses slow bcp when:
sp_dboption ‘select into/bulkcopy/pllsort’ is off.
sp_dboption ‘select into/bulkcopy/pllsort’ is on, but the table uses the allpages locking scheme and has a clustered index.
sp_dboption ‘select into/bulkcopy/pllsort’ is on, but the table has a unique nonclustered index.
If the option ignore_dup_key option is enabled on the unique index, performing fast bcp can put the table and index in an inconsistent state if rows with duplicate keys are inserted. To avoid the inconsistency, Adaptive Server performs slow bcp.
If the table has nonclustered indexes or triggers, and the table is marked for replication or the database is used as a warm standby.
Because fast bcp does not log inserts, if Adaptive Server uses fast bcp, the rows bcp copies cannot be recovered on the replication site if there is a problem. Adaptive Server uses slow bcp in these situations to maintain compatibility with applications that were written to use the old behavior.
Adaptive Server uses fast bcp when (in all cases sp_dboption ‘select into/bulkcopy/pllsort’ is enabled and the table does not have a clustered index):
You do not explicitly specify fast-logged bcp.
The table has a non-unique, nonclustered index. Adaptive Server logs the index updates and the page allocations only. It does not log inserts into the table.
A table has triggers. However, bcp does not fire any triggers in the target table.
A table has datarows or datapage locking scheme with a clustered index.
If the table includes nonclustered indexes or triggers, but sp_dboption 'select into/bulkcopy/pllsort' is not enabled, Adaptive Server uses slow bcp.
Fast bcp runs more slowly while a dump database is taking place.
Fast bcp logs only the page allocations. For copying data in, bcp is fastest if your database table has no indexes.
If you use fast bcp to make data inserts, which fast bcp does not log, you cannot back up (dump) the transaction log to a device. The changes are not in the log, and a restore cannot recover nonexistent backup data. The requested backup (dump transaction) produces an error message that instructs you to use dump database instead. This restriction remains in force until a dump database successfully completes. For more information about dump database and dump transaction, see the System Administration Guide, and the Reference Manual.
bcp optimization is performed by Adaptive Server and does not require that you use Open Client version 15.0 or later.
You can 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.
Use the set logbulkcopy {on | off } command to configure fast-logged bcp for the session. You may include the set logbulkcopy {on | off } with the --initstring 'Transact-SQL_command' parameter, which sends Transact-SQL commands to Adaptive Server before transferring the data. For example, this enables logging when you transfer the titles.txt data into the pubs2..titles table:
bcp pubs2..titles in titles.txt --initstring 'set logbulkcopy on'
The bcp program is optimized to load data into tables that do not have indexes associated with them. It loads data into tables without indexes at the fastest possible speed. Fast-logged bcp logs any data changes to the table.
When you copy data into a table that has one or more indexes, you can use fast and fast-logged bcp. This includes indexes implicitly created using the unique integrity constraint of a create table statement. However, bcp does not enforce the other integrity constraints defined for a table.
The log can grow very large during slow bcp because bcp logs inserts into a table
that has indexes. After the bulk copy completes, back up your database with dump
database, then truncate the log with dump transaction after
the bulk copy completes and after you have backed up your database
with dump database.
The performance penalty for copying data into a table that has indexes
in place can be severe. If you are copying in a very large number
of rows, it may be faster to drop all the indexes beforehand with drop
index (or alter table, for indexes
created as a unique constraint); set the database option; copy the
data into the table; re-create the indexes; and then dump
the database. Remember to allocate disk space for the construction
of indexes: about 2.2 times the amount of space needed for the data.
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.
If space is available, you can use drop index to drop all the indexes beforehand.
If you do not have enough space for the server to sort the data and build the index or indexes, use slow bcp.
