SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Database Design Guidelines for Improving OLTP Performance
To improve database performance in an online transaction processing (OLTP) environment, observe
these guidelines when you design an SQL/MX database:
• Make columns NOT NULL NOT DROPPABLE unless null values are needed. Null processing
imposes disk space and performance overhead that should be avoided whenever possible.
• Align fields on appropriate boundaries:
Align character data types as follows:◦
– Align ASCII character types on one-byte boundaries.
– Align UCS2 character types on two-byte boundaries.
◦ Align numeric data types as follows:
Align smallint on two-byte boundaries.–
– Align int on four-byte boundaries.
– Align largeint on eight-byte boundaries.
◦ Use fillers as required to attain proper alignment:
Recommended: CREATE TABLE t1, where column a is SMALLINT NOT NULL, c
is SMALLINT NOT NULL, and b is INT NOT NULL.
–
– Not recommended: CREATE TABLE t1, where column a is SMALLINT NOT NULL,
b is INT NOT NULL, and c is SMALLINT NOT NULL.
◦ Unaligned fields cannot choose bulk move.
• Avoid using signed numeric data types. Signed numbers are expensive to process and
expensive to encode for key access. Unsigned numbers do not need encoding.
• Avoid using DECIMAL data types when scale and precision are not needed. For example,
use INT instead of NUMERIC(8,0).
• Use numbers instead of characters whenever possible. For example, an INT field is better
than a CHAR(8) field for numeric employee IDs.
• Use VARCHARs carefully:
Length processing of VARCHARS is expensive.◦
◦ Use fixed character data types if maximum data length is “small.”
◦ Use VARCHARS if the variance of data size is large.
• Do not put character fields between noncharacter fields.
The C/C++ preprocessor generates an extra null terminator.◦
◦ Bulk move degradation results.
Database Design Guidelines for Improving OLTP Performance 103