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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-18
Checking Data Integrity
Performing a FUP RELOAD on a table or index reduces data fragmentation and other
structural inefficiencies and considerably decreases the duration of subsequent table
and index queries. Also, FUP RELOAD is the only method available for inserting or
altering slack space into SQL/MX files.
To improve the SQL compiler’s ability to generate accurate cost estimates and improve
table performance:
•
Perform a FUP RELOAD on each table and index at regular intervals (for example,
once a month), depending on the number of updates performed on them and other
site-specific considerations. Use the Tandem Reload Analyzer to determine when
FUP RELOAD needs to be run.
•
Perform FUP RELOADs on tables and indexes after an unusually large number of
INSERT, UPDATE, and DELETE operations have been performed on them.
Checking Data Integrity
NonStop SQL/MX provides data integrity checking when constraints are defined for a
table. When a row is added or altered, NonStop SQL/MX verifies that the new data
satisfies all constraints.
You can check data integrity within program code or with the SQL/MX constraint
mechanism. Each method has benefits and performance issues you should consider
for your application. For more information, see the information on creating constraints
in Section 7, Creating an SQL/MX Database.
Evaluate your application to determine the best use of data checking: constraints
versus program code.
The issues involved in having data integrity checks in program code or defined in the
database through constraints are:
•
Data integrity checking in program code
°
Data checking performed in the requester before sending the data to the server
is the quickest method of data checking and reduces unnecessary server calls.
The requester checks data upon input to ensure the data conforms to certain
ranges coded into the requester.
°
Data checking can be performed within an application program or server
program before the data is sent to NonStop SQL/MX. This checking reduces
unnecessary SQL calls, but still requires the program code to have the data
range values.
°
Maintaining programs, requesters, or servers to programmatically check data
input can require additional programming time. In addition, your site must have
methods or programs to verify that the existing tables conform to the new data
checks.
°
Programs with hard-coded validity checking cannot move as easily from one
set of users to another as programs without hard-coded values.