In certain circumstances, you can improve bcp performance dramatically by executing several bcp sessions with a partitioned table.
Partitioned tables improve insert performance by reducing lock contention and by distributing I/O over multiple devices. bcp performance with partitioned tables is improved primarily because of this distributed I/O.
When you execute a bcp session on a partitioned table, consider:
A partitioned table improves performance only when you are bulk copying in to the table.
The performance of slow bcp does not improve as much with partitioned tables. Instead, drop all indexes and triggers and use fast bcp, as described in Table 3-2, to increase performance.
Network traffic can quickly become a bottleneck when multiple bcp sessions are being executed. If possible, use a local connection to the Adaptive Server to avoid this bottleneck.
To copy data into a partitioned heap table, you can either:
Copy the data randomly without regard to the partition to which data is copied, or
Copy the data into a specific partition
If the table has a clustered index, bcp runs in slow mode and allows the index to control the placement of rows.
To copy data randomly into partitions
To copy data randomly into partitioned tables when using multiple bcp sessions, you must:
Configure the table with as many partitions and physical devices as you require for your system.
For more information, see the Performance and Tuning Guide, and “Using parallel bulk copy to copy data into a specific partition” of this manual.
Make sure Adaptive Server is configured with enough locks to support multiple bcp sessions. For information on configuring locks, see the System Administration Guide.
Remove the triggers and indexes on the table and enable fast bcp. See “Using fast or slow bcp” for instructions.
If
you use slow bcp, performance may not improve
significantly after you remove the triggers and indexes. Also, if
the table contains indexes, you may experience deadlocks on the
index pages.
Divide the bcp input file into as many files of equal size as the number of planned simultaneous bcp sessions.
You also can use the -F first_row and -L last_row options to specify the start and end of each “input file.”
Execute the bcp sessions with separate files in parallel on the local Adaptive Server machine.
For example, on UNIX platforms, you can execute different sessions in different shell windows or start individual bcp sessions in the background.
Read the Performance and Tuning Guide for a detailed description of copying data into partitioned tables.
