Improving Execution of Stored Procedures

When the database server executes a stored procedure language (SPL) routine with the EXECUTE PROCEDURE statement, with the SPL CALL statement, or in an SQL statement, the database server performs the following actions:

• The database server reads the interpreter code from the system catalog tables, converts it from a compressed format to an executable format and executes any SPL statements that it encounters.
• When the database server encounters an SQL statement, it parses, optimizes, and executes the statement. If the query plan for the statement has been cached for the session, the statement is not reoptimized.
• When the database server reaches the end of the SPL routine or when it encounters a RETURN statement, it returns any results to the client application. Unless the RETURN statement has a WITH RESUME clause, the SPL routine execution is complete.

The database server executes the SPL routine once for each EXECUTE PROCEDURE statement. However, the database server can execute a procedure many times if it is in a SELECT statement. The following example calls get_order_total once for each row found in the customer table:

SELECT customer_num, get_order_total(customer_num)
FROM customer;

If the SPL routine is in the WHERE clause of the SELECT statement, and it contains no parameters or uses only constants as parameters, the database server executes the SPL routine only once. The following example executes get_first_day only once:

SELECT order_num, order_date
FROM orders
WHERE order_date > get_first_day();

Not only is a SPL routine of this kind executed only once, but it is pre-executed. The database server executes the procedure and replaces the returned value in the SQL statement.

SELECT order_num, order_date
FROM orders
WHERE order_date > "10/01/97"

SQL Statements That Contain a Call to an SPL Routine

If an SQL statement contains a call to an SPL routine, the parts of the statement that are related to the SPL routine call are executed only by the connection coserver. SQL statements that are contained entirely in SPL routines might be executed in parallel, however.

The following sample query contains several calls to SPL routines:

SELECT y,x proc0(y)
FROM tab1, tab2
WHERE proc1(x) = proc2(y) AND x = proc3(x);

In this query, SELECT y, x and FROM tab1, tab2 might be parallelized, but the WHERE clause is executed by the connection coserver. This restriction does not apply to execution of SQL statements that are contained in the SPL routines themselves, proc1(), proc2(), and proc3().

SQL Statements in an SPL Routine

The optimizer can execute the SQL statements that are contained in an SPL routine in parallel. The degree of parallelism depends on factors such as the SQL operators that make up the query plan for the individual SQL statement, the fragmentation strategy of the tables that are involved in each SQL statement, the number of CPU virtual processors available, and other resource availability.

The PDQPRIORITY setting controls the amount of memory that a query can use. Queries specified with a low PDQPRIORITY value request proportionally smaller amounts of memory, so more of those queries can run simultaneously.

To change the client value of PDQPRIORITY, embed the SET PDQPRIORITY statement in the body of the procedure.

For example, the following procedure contains several SET PDQPRIORITY statements:

CREATE PROCEDURE my_proc (a INT, b INT, c INT)
Returning INT, INT, INT;
SET PDQPRIORITY 0;
...
SET PDQPRIORITY 85;
SELECT ... (big, complicated, memory-consuming
SELECT statement)
SET PDQPRIORITY 0;
....