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-27
Recommended Practices for Improving import
Performance
Importing Data Into Multiple Hash Partitions
Because imported data cannot be presorted for hash-partitioned tables, using a single
import instance for a hash-partitioned table is generally more efficient than running
multiple instances in parallel. If you do run parallel instances, keep the transaction size
to a minimum to avoid lock escalation to the table level.
After running import operations on a hash-partitioned table, perform a FUP RELOAD
on the affected partitions of the table to make the table file’s b-tree structure more
efficient and improve DML performance.
Managing Constraints to Improve import Performance
Consider these guidelines for improving import performance with constraints:
•
Each check constraint, including NOT NULL constraints, causes an extra check of
the data before the import operation completes. Because no additional I/O is
needed to process the data, check constraint checking should be relatively
efficient. However, it does increase the time required to process each table row.
•
Droppable primary key constraints, unique constraints, and RI constraints usually
require an index to handle validation. RI constraints do not create an index if the
foreign key is on the table’s clustering key. Index maintenance degrades import
performance. For more information, see Managing Indexes to Improve import
Performance on page 10-27.
•
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 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.