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-25
Recommended Practices for Improving import
Performance
Running import on Empty Tables
You can import data into a table most quickly by using the fast load technique, which
import automatically uses when the table meets all these conditions and the
transaction size is not specified:
•
It is empty.
•
It has no indexes.
•
It has no droppable primary key, unique key, or foreign key constraints.
•
It has no enabled triggers.
To further improve the performance of the fast load technique, import turns off the
audit attribute for the entire table at the start of the operation and turns it back on when
the operation ends.
If, while you are performing an import operation with the fast load technique, another
import operation is attempted on the same table, the second operation fails with a
concurrent access error.
To improve the performance of an import operation on an empty table:
•
Verify that all data being imported is contained in a single flat input file. OSS flat
files cannot exceed 2 GB. If the input file exceeds 2 GB, consider using
DataLoader/MX, which supports larger Guardian files. For information about using
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 load 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: