SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-42
Database Design Guidelines for Improving OLTP
Performance
•
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.
•
Use datetime fields carefully:
°
Datetime operations are expensive.