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

◦ 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.
• Data integrity checking by constraints
Constraints can greatly enhance the flexibility of programs so that applications move
easily from one set of users to another.
◦
◦ Constraints simplify the change process to a simple, online process. If you add one
constraint, the system immediately applies the constraint to all subsequent transactions.
The constraint creation process also checks the existing table to ensure that all existing
rows conform to the new constraint.
◦ When NonStop SQL/MX verifies the constraints on the input data, the potential message
traffic between servers and requesters might be increased when error messages are
generated on invalid data.
Creating Logical Views of Data
Logical views of the database are groupings of data different from the physical database. NonStop
SQL/MX is efficient at presenting data in logical views, joining tables or other views to create a
new window into the data. These logical views can specify only those columns or rows of data
that meet given criteria. NonStop SQL/MX returns only the subset of data, if any, that meets the
criteria, thereby reducing message data transfer between the DP2 process and your program.
You can predefine and name logical views with the CREATE VIEW statement, or you can create
views logically with a SELECT statement. The performance of these two methods to obtain the same
data is equivalent.
For more information, see “Creating an SQL/MX Database” (page 72).
Adding and Dropping Partitions
For performance improvement, consider partitioning a table or index so that the table or index
spans multiple volumes or multiple nodes. As the number of rows in the table or index increases,
consider redistributing rows across partitions to balance the distribution of rows. For
range-partitioned tables and indexes, you can use the MODIFY TABLE and MODIFY INDEX
statements to split partitions, move partitions, and move partition boundaries. For hash-partitioned
tables and indexes, you can use MODIFY to add, drop, or move partitions. For range-partitioned
tables, you can use the REUSE option.
When all information in a partition becomes obsolete, or when a database design deficiency
leaves a partition continually empty, a reference to a table or index defined across this partition
results in an unnecessary message to the partition. This message, in turn, results in a correspondingly
longer access time to the table or index. In such circumstances, you might want to drop this partition
while leaving the others defined for the object intact.
Avoiding Automatic Recompilations
Automatic recompilation can become a significant performance concern. In most cases, you should
attempt to run valid programs at all times to ensure the best possible performance.
Automatic recompilation makes it possible for application programs to continue to perform when
invalidating events occur or when access paths are unavailable. The time required to perform the
recompilation, however, can noticeably add to the initial response time of the application program
that contains the SQL statements.
304 Enhancing SQL/MX Database Performance