Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 5: Performance Considerations
Home
Contents
Index
Master Index
New Book
SPL Considerations
This section describes the compilation, execution, and optimization processes of an
SPL
routine.
Tip:
Not all
the encapsulated
SPL
that you created as
SPL
procedures in earlier Informix products has the properties currently associated with procedures. If the
SPL
routine returns a value, you now refer to it as an SPL function. If the
SPL
routine
does not return a value, you still refer to it as an
SPL
procedure.
SPL Compilation
Universal Server compiles an
SPL
routine when you execute the
CREATE PROCEDURE
or
CREATE FUNCTION
. The following activities occur in the compilation process of the
SPL
routine:
Parse and optimize, if possible, all
SQL
statements
The database server puts the
SQL
statements
into an
execution plan.
An execution plan is a structure that enables the database server to store and execute the
SQL
statements efficiently.
The database server optimizes each
SQL
statement within the
SPL
routine and includes the selected query plan in the execution plan. For more information on
SPL
routine optimization, refer to
"SPL Optimization"
.
Build a dependency list
A dependency list contains items that the database server checks to decide if an
SPL
routine needs to be reoptimized at execution time. For example, the database server checks for the existence of all tables, indexes, and columns involved in the query.
Parse
SPL
statements and convert to pcode
The term
pcode
refers to pseudocode that an interpreter executes quickly.
Convert the pcode, execution plan, and dependency list to
ASCII
format
The database server stores these
ASCII
formats as character columns in the system catalog tables,
sysprocbody
and
sysprocplan
.
Store general information about the procedure in the
sysprocedures
system catalog table
Store permissions for the procedure in the
sysprocauth
system catalog table
SPL Execution
When you execute an
SPL
routine with the
EXECUTE FUNCTION
,
EXECUTE ROUTINE, EXECUTE PROCEDURE,
or
CALL
statement, the database server performs the following tasks:
Retrieves the pcode, execution plan, and dependency list from the system catalog and converts them to binary format
Parses and evaluates the arguments passed by the
EXECUTE FUNCTION
,
EXECUTE ROUTINE, EXECUTE PROCEDURE,
or
CALL
statement
Checks the dependency list for each
SQL
statement that will be executed
If an item in the dependency list indicates that reoptimization is needed, optimization occurs at this point.
If an item needed in the execution of the
SQL
statement
is missing (for example, a column or table has been dropped), an error occurs at this time.
The interpreter executes the pcode instructions.
SPL Optimization
A
query plan
is a specific way that a query might be performed. A query plan includes how to access tables, the order of joining tables, and the use of temporary tables. During
SPL
optimization, the query optimizer evaluates the possible query plans and selects the query plan with the lowest cost. The database server puts the selected query plan for each SQL statement into an execution plan for the SPL routine.
Optimization Levels
The current optimization level set in an
SPL
routine affects how the
SPL
routine is optimized.
The algorithm that a
SET
OPTIMIZATION
HIGH
statement invokes is a sophisticated, cost-based strategy that examines all reasonable query plans and selects the best overall alternative. For large joins, this algorithm can incur more overhead than desired. In extreme cases, you can run out of memory.
The alternative algorithm that a
SET
OPTIMIZATION
LOW
statement invokes eliminates unlikely join strategies during the early stages, which reduces the time and resources spent during optimization. However, when you specify a low level of optimization, the optimal strategy might not be selected because it was eliminated from consideration during early stages of the algorithm.
For
SPL
routines that remain unchanged or change only slightly, you might want to set the
SET OPTIMIZATION
statement to
HIGH
when you create the routine. This optimization level stores the best query plans for the routine. Then set optimization to
LOW
before you execute the routine. The routine then uses the optimal query plans and runs at the more cost-effective rate if reoptimization occurs.
When Optimization Occurs Automatically
When you create an
SPL
routine, the database server attempts to optimize the
SQL
statements within the routine at that time. If the tables cannot be examined at compile time (they might not exist or might not be available), the creation does not fail. In this case, the database server optimizes the
SQL
statements
the first time that the
SPL
routine executes. The database server stores the optimized execution plan in the
sysprocplan
system catalog table for use by other processes.
The database server uses the dependency list to keep track of changes that would cause reoptimization the next time that an
SPL
routine executes. The database server reoptimizes an
SQL
statement the next time that an
SPL
routine executes after one of the following situations:
Execution of any Data Definition Language (
DDL)
statement (such as
ALTER TABLE, DROP INDEX, CREATE INDEX
) that might alter the query plan
Alteration of a table that is linked to another table with a referential constraint (in either direction)
Execution of
UPDATE STATISTICS FOR TABLE
for any table involved in the query
The
UPDATE STATISTICS FOR TABLE
statement changes the version number of the specified table in
systables.
The database server updates the
sysprocplan
system catalog table with the reoptimized execution plan.
Updating Statistics for a UDR
The database server stores statistics about the amount and nature of the data in a table in the
systables
,
syscolumns,
and
sysindices
system catalog tables. The statistics that the database server stores include the following information:
Number of rows
Maximum and minimum values of columns
Number of unique values
Indexes that exist on a table, including the columns and functional values that are part of the index key
The query optimizer uses these statistics to determine the cost of each possible query plan. Run
UPDATE STATISTICS
to update these values whenever you have made a large number of changes to the table.
If you do not run
UPDATE STATISTICS
after the size or content of any table changes, no
SQL
statements within the
SPL
routine are reoptimized. The next time a routine executes, the database server reoptimizes its execution plan if any objects that are referenced in the procedure have changed.
The various clauses on the
UPDATE STATISTICS
statement influence re-optimization in the following ways:
UPDATE STATISTICS
When you specify no clauses, the database server reoptimizes
SQL
statements in all
SPL
routines and changes the statistics for all tables.
UPDATE STATISTICS FOR TABLE
table name
When you specify a table name in the
FOR TABLE
clause, the database server changes the statistics for the specified table.This statement does not reoptimize any
SQL
statements in
SPL
routines.
UPDATE STATISTICS FOR TABLE
When you specify the
FOR TABLE
clause without a table name, the database server changes the statistics for all tables and does not reoptimize any
SQL
statements in
SPL
routines.
UPDATE STATISTICS FOR
routine statistics
name
When you specify one of the following clauses with a name, the database server reoptimizes
SQL
statements in only the
SPL
routine listed:
FOR FUNCTION
routine name
clause
FOR PROCEDURE
routine name
clause
FOR ROUTINE
routine name
clause
When you specify one of these clauses, the database server does not update the statistics in the system catalog tables.
UPDATE STATISTICS FOR
routine statistics
When you specify one of the following clauses without a name, the database server reoptimizes
SQL
statements in all
SPL
routines:
FOR FUNCTION
clause
FOR PROCEDURE
clause
FOR ROUTINE
clause
When you specify one of these clauses, the database server does not update the statistics in the system catalog tables.
The database server updates the
sysprocplan
system catalog table with the reoptimized execution plan.
Extending INFORMIX-Universal Server: User-Defined Routines
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
