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

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-14
Creating Logical Views of Data
°
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 disk 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 the information on creating views of tables in Section 7,
Creating an SQL/MX Database.
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.