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-30
Recommended Practices for Improving import
Performance
performance. For more information, see Managing Indexes to Improve import
Performance on page 10-30.
•
RI constraints require a lookup of each row of the referenced table to confirm that
the row exists, which imposes an extra I/O for each row being inserted. The extra
I/O can impose a significant performance penalty when running import on a table
with one or more RI constraints. If necessary, drop the RI constraint first and add it
back after the import operation completes.
Managing Indexes to Improve import Performance
A given table with one or more indexes takes much longer to load with import than
does the same table without indexes.
When you perform an import operation on an SQL/MX table with indexes, you should
first drop all indexes, and then re-create them after the operation completes:
1. Perform a SHOWDDL command on the table and save the SHOWDDL output.
2. Drop all indexes on the table.
3. Perform all required import operations on the table.
4. After the import operations complete, refer to the SHOWDDL output to locate all
the CREATE INDEX statements. Update the CREATE INDEX statements and add
the NO POPULATE option.
5. Re-create the indexes.
6. Run POPULATE INDEX to load the data into the indexes.
For information about CREATE INDEX and the NO POPULATE option, see the
SQL/MX Reference Manual.
Managing Triggers to Improve import Performance
Triggers degrade import performance by causing triggered actions to be performed
during the operation. When you perform an import operation on a table with triggers,
use the import -D option to disable all triggers. After the data has been inserted,
import automatically enables the disabled triggers.
Managing Data Types to Improve import Performance
import requires additional processing for all date time (DATE, TIME, and
TIMESTAMP), numeric, and decimal data types. import reads the format file, extracts
the value of the input file, and converts it to a form recognized by NonStop SQL/MX.
For numeric and decimal data types, import converts the value to the scale and
precision required by NonStop SQL/MX.