Using fast mode in Sybase bcp


In Sybase, when you have indexes on tables it affects transfer speed. When bcp is used on such tables, it automatically uses its slow mode and logs data is inserted 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.

Note that 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 incase database table doesn’t contain indexes.

In Sybase ASE 15.0.2 fast bcp is allowed only for indexed tables. All the other things related to bcp remain same like select into/bulkcopy/pllsort is enabled and logging only the page allocations in log etc.

Comparison fast and slow bcp −

Database configuration for fast bcp −
To use fast bcp, you should use sp_dboption to set select into/bulkcopy/pllsort to true on the database that contains the target table or tables. When you set this option to “false” and a user tries to use fast bcp to copy data into a table without indexes, an error message is shown by the Adaptive server.

When you set up a new database, select into/bulkcopy/pllsort option is set to false initially. You can change default setting by turning this option to true in the model database.
To know more about SAP Sybase performance bcp performance, you can also refer to this link −

Published on 19-Dec-2017 12:13:38