Introduction to NonStop SQL/MP

What Happens When a Query Is Submitted
NonStop SQL Architecture
113425 Tandem Computers Incorporated 3–11
First, whenever an SQL statement is invoked by the application, the SQL executor can
immediately execute that query plan without having to recompile it. This strategy
improves performance, especially for OLTP and electronic commerce applications in
which the same transaction type (and SQL statements) can execute hundreds or
thousands of times a day.
Second, keeping the SQL source statements in the program file makes it easy for the
SQL executor to access a statement if the statement should need to be recompiled.
Occasionally, the DBA might need to change a table (or another SQL object) in such a
way that the existing SQL query plan would not access the changed table efficiently or
properly. Because the data dictionary keeps track of programs (and their embedded
query plans) that access SQL objects, the SQL executor can recognize an out-of-date
query plan and can automatically recompile the SQL statement at run time. The SQL
executor can retrieve the source statement directly from the object program file.
If you use certain compiler options, the SQL executor checks the existing query plan
against the changed table. If the change does not affect the existing plan—that is, if the
plan will execute properly—the SQL executor reuses it, saving the cost of the
recompilation.
These architectural features help to provide both high performance and high
availability, a main objective of NonStop SQL/MP.
The SQL Optimizer
When a query is compiled, the SQL optimizer, a component of the SQL compiler,
generates an access plan for the query. The SQL optimizer is cost-based; it evaluates
the query and determines the most efficient plan for retrieving data from the database.
The optimizer estimates the number of I/O operations, CPU resources, and
interprocess messages needed to execute each plan it considers for a query; it then
selects the best plan.
A typical query can join tables, set conditions for selection in WHERE clauses, and
include subqueries (SELECT statements nested within the main SELECT statement). A
query may also ask to group or order data (which may involve sorting) or calculate an
aggregate value such as a sum or average. These elements in a query can be executed
in different ways and in different sequences that affect the execution performance.
The size and configuration of the database also affect the efficiency of a given access
plan. For example, the tables referenced in the query can have indexes and be
partitioned.
Figure 3-5 shows a simple example of the generation and selection of an access plan.
In the example, the optimizer selects Plan C, which has the lowest execution cost.