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
or semi-permanent sections.
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.
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'''
|
