SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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 the “Managing Indexes to Improve import Performance” (page 197).
• 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 more information, 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.
Using import to Load Partitions
• For range-partitioned tables, you can load the data from a single input file or several input
files into a table’s range partitions. The data in the input file or files must first be ordered by
the table’s range partitioning key.
To perform a parallel import from a single input file or a serial import from multiple input
files, use the -F option to specify the number of rows to be skipped at the beginning of the
input file. Use the -C option to specify the number of rows to be imported after the skipping
occurs. You can use the -F and -C options to instruct import to use the specified rows to
Using import to Load SQL/MX Tables 197