SQL/MP Query Guide

Analyzing Query Performance

HP NonStop SQL/MP Query Guide—524488-003

6-3

Preparing Your Queries

Before you test your queries, you should prepare them using the SQLCI PREPARE

command.

The PREPARE command compiles an SQL statement and assigns a name to the

statement. You can then reference the statement name to execute the statement

multiple times without recompiling, and you can obtain an EXPLAIN plan for the

compiled statement. These steps outline this procedure:

1. Prepare the query:

>> PREPARE QUERY FROM

+> SELECT * FROM EMPLOYEE ;

2. Obtain EXPLAIN plan for compiled query:

>> EXPLAIN PLAN FOR QUERY ;

3. Execute the query:

>> EXECUTE QUERY ;

4. Display execution statistics for the query:

>> DISPLAY STATISTICS ;

Details on analyzing the EXPLAIN plan and execution statistics follow.

Using DISPLAY STATISTICS

The DISPLAY STATISTICS command provides statistics for an executed query. You do

not need to prepare a statement to use DISPLAY STATISTICS. However, if you want to

execute the statement more than once by referring to the statement name, you should

first prepare the statement. You can also refer to the statement name to generate an

EXPLAIN plan.

These queries retrieve data from the EMPLOYEE table of the sample database

provided with the SQL software. The primary key of the EMPLOYEE table is

EMPNUM.