The existence of indexes on tables affects transfer speed. When you use bcp on such tables, bcp automatically uses its 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.
bcp does
not fire any trigger that exists on the target table.
Fast bcp logs only the page allocations. For copying data in, bcp is fastest if your database table has no indexes.
However, if you used 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.
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.
These are situations in which Adaptive Server uses fast bcp (in all cases sp_dboption ‘select into/bulkcopy/pllsort’ is enabled and the table does not have a clustered index):
When 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.
When a table has triggers. However, bcp does not fire any triggers in the target table.
When 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.
bcp optimization is performed by Adaptive Server and does not require that you use Open Client version 15.0 or later.
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, with a minimum of logging. Page allocations are logged, but the insertion of rows is not.
When you copy data into a table that has one or more indexes, you can use fast 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.
By default, the select into/bulkcopy/pllsort option is false (off) in newly created databases. To change the default setting for future databases, turn this option on in the model database.
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.
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.
Fast bcp runs more slowly while a dump database is taking place.
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.
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.
To allow a user to copy in data using fast bcp, either a System Administrator or the Database Owner first must use sp_dboption to set select into/bulkcopy/pllsort to true on the database that contains the target table or tables. If the option is set to false when a user tries to use fast bcp to copy data into a table without indexes, Adaptive Server generates an error message.
You do not need to 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.
By default, the select into/bulkcopy/pllsort option is set to false (off) in newly created databases. To change the default setting for future databases, turn this option on (set to true) in the model database.
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.
