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 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 or fast-logged bcp.
Network traffic can quickly become a bottleneck when multiple bcp sessions are being executed. If possible, use a local connection to the SAP ASE 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.

Copying Data Randomly Into Partitions
You can copy data randomly into partitioned tables when using multiple bcp sessions.
Monitoring bcp Sessions with dbcc checktable and sp_helpsegment
If you do not specify which partition the bcp sessions should use, the SAP ASE server randomly assigns the multiple bcp sessions to the table’s available partitions.
Reducing Logging by Increasing Page Allocations
If you are using fast or fast-logged 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.

Parent topic: Improve bcp Performance

Related concepts

Utility Commands Reference