Home

	Expand Search

Utility Guide

Chapter 3 Using bcp to Transfer Data to and from Adaptive Server

bcp performance

Bulk copying data into partitioned tables

Bulk copying data into partitioned tables

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 partitioned tables when using multiple bcp sessions, you must:

1. 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.

2. Make sure Adaptive Server is configured with enough locks to support multiple bcp sessions. For information on configuring locks, see the System Administration Guide.

3. 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.

4. 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."

5. 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.

If you do not specify which partition the bcp sessions should use, Adaptive Server randomly assigns the multiple bcp sessions to the table's available partitions. If this random assignment occurs, be sure to monitor the partitions to ensure that the process has evenly distributed the inserts by using either of the following:

• dbcc checktable - to periodically to check the total page counts for each partition

• sp_helpsegment or sp_helpartition - to perform a similar check, but without locking the database objects

For more information about dbcc checktable, see the System Administration Guide. For more information about sp_helpsegment and sp_helpartition, see the Reference Manual.

For more information about table partitions, see the Performance and Tuning Guide.

If you are using fast bcp, consider that each bcp in batch requires the page manager to allocate one or more extents. Each such allocation generates a single log record.

Use the number of preallocated extents configuration parameter to specify how many extents Adaptive Server is to allocate through the page manager.

• Valid values for the number of preallocated extents configuration parameter are from 0 to 31; the default value is 2.

• You must restart Adaptive Server to change the value.

• When performing large bcp operations, increase this number to prevent the page allocations from filling the log.

• Set this value to 0 to prevent large extent allocations, so that the page manager performs only single-page allocations.

Adaptive Server may allocate more pages than are actually needed, so keep the value small when space is limited. These pages are deallocated at the end of the batch.

For more information, see the System Administration Guide.