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 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 use fast bcp, as described in Table 4-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.
When copying data into a partitioned table, you can:
Copy the data randomly without regard to the partition to which data is copied. For example, to copy data from file1 to bigtable, enter:
bcp mydb..bigtable in file1
To copy data from file1, file2, and file3 to bigtable, enter:
bcp mydb..bigtable in file1, file2, file3
Copy the data into a specific partition For example, to copy data from file1 to ptn1, file2 to ptn2, and file3 to ptn3, enter:
bcp mydb..bigtable partition ptn1, ptn2, ptn3 in file1, file2, file3
To copy data from file1 to the first partition of bigtable, enter:
bcp mydb..bigtable:1 in file1
If the table has a clustered index, bcp runs in slow mode and allows the index to control the placement of rows.
See Chapter 7, “Utility Commands Reference,” for complete syntax and usage information for bcp.
Copying 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 indexes on the table and enable fast bcp. See “Using fast or slow bcp” for instructions.
If
you use slow bcp,
performance may improve significantly after you remove the indexes.
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.
