SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-26
Recommended Practices for Improving import
Performance
•
The amount of data being imported. When you import a small amount of data (for
example, less than 500,000 rows), you can either perform the import operation in
parallel or in a single stream. When you import large amounts of data, you need to
examine the data being imported and the partitions into which it will be inserted.
For example, examine the range of data being imported and the number of
partitions being loaded to determine where the data should go.
•
Whether you are inserting the data into a single partition or multiple partitions. The
method for inserting data into a single partition is different from the method for
inserting data into multiple partitions.
Managing Partitions to Improve import Performance
Importing Data Into a Nonpartitioned Table
Before you import data into a nonpartitioned table, you should run a single instance of
import with all the data contained in a single input file.
When you import data into a single range partition, sort the data in the input file
according to the clustering key order to avoid extra sorting. Make sure that the attribute
UPD_ORDERED is set to OFF.
Importing Data Into Multiple Range Partitions
Before you import data into a table with multiple range partitions, first identify all
affected partitions and where they are located in the table.
When you use import to load multiple partitions, running multiple instances of
import is usually faster than running a single instance.
If you are running NonStop SQL/MX on a single node, you have these options for
using import with range-partitioned tables:
•
Put the input data into several files, each of which is destined for a different
partition. Run an import instance for each partition.
•
Put the input data into several files, each of which is destined for all the partitions
that reside on a particular disk. Run an import instance for each disk.
If you are running import in a distributed SQL/MX environment:
•
For optimum speed, always run import on the node where the table partitions to
be loaded are located. Avoid running import instances on local table partitions
from a remote node.
•
The greater the number of nodes running import, the slower its overall
performance.
As is the case with loading single partitions, import runs faster when input data for
multiple range partitions is sorted according to clustering key order.