SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-3
Preparing Queries
The query returns this result:
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ ------------- ------------- ------- ------- ----------
1 ROGER GREEN 9000 100 175500.00
. . . .
. . . .
568 JESSICA CRINER 3500 300 39500.00
--- 57 row(s) selected.
To improve the performance of the query, you can refine the query by specifying only
the columns you really need, reducing the amount of data SQL must return.
Suppose that the only items you are really interested in are the employee’s
identification number, last name, first name, and job category for employees in
department 3000. You can request this information by entering
SELECT empnum, last_name, first_name, jobcode
FROM employee
WHERE deptnum = 3000 ;
The query returns this result:
EMPNUM LAST_NAME FIRST_NAME JOBCODE
------ ------------- ------------- -------
29 RAYMOND JANE 100
75 WALKER TIM 300
201 HERMAN JIM 300
343 TERRY ALAN 900
--- 4 row(s) selected.
As you refine the query to return only the data you require, the number of rows that
satisfy the query is reduced, which makes the query more efficient and diminishes the
processing overhead on the system.
Preparing Queries
For SQLCI and dynamic SQL users, if you plan to use the same query repeatedly, you
can prepare the statement and cause SQL to save a compiled version of it for later
use. By doing so, you increase the efficiency of subsequent use of the query. You can
do this from within an SQLCI session or from a program. For example, this statement
prepares a query and associates the name Q1 with it:
>> PREPARE Q1 FROM
+> SELECT * FROM EMPLOYEE ;
To execute the query, enter this statement:
>> EXECUTE Q1 ;
To explain the query, enter this statement:
EXPLAIN Q1 ;