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

Another disadvantage of co-located indexes is that if a table partition is moved to another disk,
you must remember to move its corresponding index partition.
Performing FUP RELOADs to Generate More Accurate Query Plans
As tables and indexes are subjected to repeated INSERT and DELETE operations, they become
more fragmented and less efficiently organized. Over a period of weeks, it can become impossible
for MXCMP to accurately estimate the costs for different query plans for those tables. In effect, the
B-trees for the tables become too deep and asymmetrical.
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 “Creating an SQL/MX Database” (page 73).
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.
306 Enhancing SQL/MX Database Performance