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

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—544536-007
10-28
Recommended Practices for Improving import
Performance
DataLoader/MX with import to load and maintain SQL/MP and SQL/MX
databases, see the DataLoader/MX Reference Manual.
•
For an empty range-partitioned table:
1. Make sure the data in the input file is presorted according to the clustering key
order if a clustering key is present. Presorting the input file data prevents
external sorts and extra block splits. When DP2 inserts unordered data into a
partition, import performance is slowed.
2. Use the CONTROL QUERY DEFAULT statement to set the UPD_ORDERED
attribute to OFF. When the UPD_ORDERED attribute is ON, the ESP may
respond by sorting imported data.
3. You must redo your online dumps for all partitions that are loaded using the
fast loading technique (turning off audit invalidates online dumps).
4. If the import operation fails because the process dies, you must run
RECOVER to cancel or resume the import operation.
Running import on Populated Tables
To speed up the performance of import on populated tables, you must properly
manage SQL/MX objects that are known to slow down performance, including
constraints, indexes, triggers, and certain data types.
These factors determine the specific method to use for importing data into a populated
table:
•
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
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.