SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-2
Guidelines for Tuning Queries
•
EXPLAIN Plan for Cursor DELETE on page 6-73
•
EXPLAIN Plan for INSERT on page 6-74
•
EXPLAIN Plan for INSERT-SELECT on page 6-75
•
EXPLAIN Plan for UPDATE on page 6-77
•
EXPLAIN Plan With Date-Time Values on page 6-78
•
Comparing Cost: A Scenario on page 6-80
Guidelines for Tuning Queries
When examining and tuning queries, use available tools such as these:
•
The DISPLAY STATISTICS command, which displays run time statistics about the
last DML command you executed.
•
The Measure product, which collects statistical information about SQL database
objects and processes, and generates reports about them.
•
The EXPLAIN utility, which provides detailed information about the optimizer-
generated query execution plan for a compiled query.
The rules for tuning SQL statements can be summarized as follows:
•
For new queries, prototype, prepare, and test the statements. Optimally, use
EXPLAIN and run the statements against production data before incorporating the
query into a program.
•
For queries already in use, obtain Measure information and categorize your
programs based on:
°
High consumption of system resources
°
Poor performance and critical priority
°
High volume queries
Focus ongoing performance work on these areas:
•
High-impact queries (these might be most-used, highest system resources, longest
response times, or most critical queries)
•
Queries being migrated to production
•
Review plans after an UPDATE STATISTICS operation