The existence of indexes and triggers 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 or triggers.
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 Adaptive Server Enterprise System Administration Guide, and the Adaptive Server Enteprise Reference Manual.
The bcp program is optimized to load data into tables that do not have indexes or triggers associated with them. It loads data into tables without indexes or triggers 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 or triggers, a slower version of bcp is automatically used, which logs row inserts. 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 situation, 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 or triggers. 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.
Table 3-1 shows which version bcp uses when copying in, the necessary settings for the select into/bulkcopy/pllsort option, and whether the transaction log is kept and can be dumped.
select into/bulkcopy/pllsort |
on |
off |
|---|---|---|
fast bcp (no indexes or triggers on target table) |
OK dump transaction prohibited |
bcp prohibited dump transaction |
slow bcp (one or more indexes or triggers) |
OK dump transaction prohibited |
OK dump transaction OK |
The performance penalty for copying data into a table
that has indexes or triggers 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 and triggers beforehand with drop index (or alter
table, for indexes created as a unique constraint) and drop
trigger; set the database option; copy the data into the
table; re-create the indexes and triggers; and then dump
the database. Remember to allocate disk space for the construction
of indexes and triggers: 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 or triggers, 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 or triggers. Slow bcp always
copies tables with indexes or triggers 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 and drop trigger to drop all the indexes and triggers beforehand.
If you do not have enough space for the server to sort the data and build the index or indexes, use slow bcp.
