SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-8
Improving Query Performance
°
Target and source INSERT match:
INSERT INTO t(col) VALUES (:hvar)
°
UPDATE match:
UPDATE t SET col = :hvar ...
•
Set different default values. Default values can affect both compile and run time.
Performance-related default values include OPTIMIZATION_LEVEL, which
indicates the effort the optimizer should use in optimizing queries. Others include
default settings related to parallelism. For information about default settings, see
the SQL/MX Reference Manual.
•
Adding or dropping partitions can influence whether you obtain a parallel plan. For
more information about parallel plans, see Section 8, Parallelism.
•
If you use a key column in an expression like WHERE A*3=10 or WHERE
SIN(A)=20, or UPSHIFT(key col), or another similar expression that requires
computation or a function like UPSHIFT, a full table scan is used, and key access
is not used. As a result, query performance can be degraded. Consider rewriting
the expression in the form col=constant.
•
Whenever possible, formulate your queries to use multiplication instead of division.
For example, this query that uses division can be reformulated to use
multiplication:
--DIVISION
SELECT A/10 FROM T WHERE B > C/100
--REFORMULATED TO MULTIPLICATION
SELECT A*0.1 FROM T WHERE B > C*0.01
•
When questioning “bad plans,” or query plans that do not seem optimal, question
the inputs to the compiler before you question the compiler.
Enabling Online Transaction (OLT) Optimization
If your OLTP application is having performance problems, verify that the token
olt_optimization is enabled for single-row access type queries. The token is present at
three levels: in the ROOT operator, PARTITION_ACCESS operator, and DAM
operators (INDEX_SCAN_UNIQUE, INSERT, FILE_SCAN_UNIQUE,
UNIQUE_DELETE, and UNIQUE_UPDATE).
The CONTROL QUERY DEFAULT to enable OLT optimization is OLT_QUERY_OPT
and is on by default. To check if OLT optimization is actually being used, look for
olt_optimization: USED in the DISPLAY_EXPLAIN output.