HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

Tuning Performance of Dynamic Statements 

A given ALLBASE/SQL statement can be processed either statically or
dynamically.  At run time, a dynamic statement must be preprocessed
before executing; therefore, a static section may offer better
performance.  However, dynamic processing is often desirable for reasons
of portability or flexibility.  (For a comparison of the two types of
statements, see the "Comparing Static and Dynamic Statements" section of
the ALLBASE/SQL Advanced Application Programming Guide.)

When your application must execute a dynamic statement more than once,
you can achieve improved performance by using dynamic parameters[REV BEG]
or semi-permanent sections.[REV END]

Using Dynamic Parameters 

When your application uses dynamic processing, parameter substitution
offers added flexibility and improved performance.  Although you can use
this technique in any dynamic processing application involving prepared
sections, it could be most useful for applications where the same SQL
statement type must be re-executed multiple times using a different set
of actual parameter values each time.

A statement containing dynamic parameters must be dynamically
preprocessed at run time by using the PREPARE statement.  The dynamic
section created by PREPARE can then be executed as many times as required
with the option of assigning a different set of dynamic parameter values
for each execution without the overhead of preprocessing each time input
values change.

For example, the following UPDATE statement specifying two dynamic
parameters could be put into either a string or a host variable (in this
case a string) in your program, then prepared and executed:

     PREPARE Cmd FROM
     'UPDATE PurchDB.Parts SET SalesPrice = ? WHERE PartNumber = ?;'

     Execute the dynamic command using host variables 
     to provide dynamic parameter values: 

     EXECUTE Cmd USING :SalesPrice, :PartNumber

     You could now loop back to provide different values 
     for SalesPrice and PartNumber.  Note that the dynamic 
     command does not have to be prepared again. 

When your application will be inserting multiple rows of data, you might
be able to use a BULK INSERT statement containing dynamic parameters to
provide efficient performance.

The chapter "Using Parameter Substitution in Dynamic Statements" in the
ALLBASE/SQL Advanced Application Programming Guide contains further
information including detailed code examples.
[REV BEG]

Using Semi-Permanent Sections 

Semi-permanent sections improve performance when your application
executes dynamic queries more than once.  Unlike temporary sections,
semi-permanent sections are retained in memory when the current
transaction ends.  Semi-permanent sections are deleted from memory only
when the DBEnvironment session ends.  Semi-permanent sections, like
temporary sections, are not stored in the DBEnvironment.  When using
semi-permanent sections, set the Authorize Once per Session flag to ON
with the SQLUtil ALTDBE command:

     >> ALTDBE 
     DBEnvironment Name: PartsDBE 
     .
     .
     .
     Authorize Once per Session (on/off) (opt):on 
     Alter DBEnvironment Startup Parameters (y/n)?y 
     >>

To make a section semi-permanent, include the REPEAT clause in the
PREPARE statement.  In the following example, the section containing the
UPDATE statement is semi-permanent:

     PREPARE REPEAT Cmd FROM
     'UPDATE PurchDB.Parts SET SalesPrice = 100.00 WHERE PartNumber = ''1124-P-02'''
[REV END]

MPE/iX 5.0 Documentation