HP 3000 Manuals

ALLBASE/SQL Performance 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 Release F.0 Application Programming Bulletin.)

When your application must execute a dynamic statement more than once,
you can achieve improved performance by using dynamic parameters within
the transaction.

Using Dynamic Parameters Within a Transaction 

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
within a given transaction 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 Release F.0 Application Programming Bulletin contains further
information including detailed code examples.

MPE/iX 5.0 Documentation