Tuning SQL Database Access

#### Search Results

Search

# Tuning SQL Database Access

By default, the Natural SQL driver manages a table with the 16 most recently used Natural statements. All statements in this table are marked as prepared, which indicates that the statement can be executed immediately without being compiled by the database system.

To ensure maximum performance, the dynamic parameters `SQLRELCMD` and `SQLMAXSTMT` are provided. These parameters configure the handling of the SQL driver's statement table. Note that these parameters are not profile parameters.

## SQLRELCMD

This parameter determines when commands are to be released from the SQL statement table.

Possible values:

- `ENDGP` (default): if a generated program terminates, all statements from this program that are in the statement table are removed from the table.

- `NEVER`: No statement will be deleted from the table.

## SQLMAXSTMT

This parameter determines the size of the statement table.

Possible values are 1 to 64 (default: 16).

If you set the `SQLMAXSTMT` parameter, keep the following in mind:

- Resource consumption may be higher if you are keeping more prepared statements in the table.

- If the size of the statement table exceeds the limit of dynamic SQL statements in the target database, the application will receive SQL errors.

- It depends on the database whether there is a real benefit from the `SQLMAXSTMT` optimization.

- In general, performance in batch-type applications will be improved if the number of `PREPARE` statements is minimized, while performance in online applications will probably be worse because of the increased resource consumption of the target database.

## Example

To set the above parameters dynamically, enter them when starting Natural:

    natural sqlrelcmd=never sqlmaxstmt=40

Natural will then start with a statement table size of 40 and the statement table will only be cleared when Natural is terminated.

### On this page

- SQLRELCMD
- SQLMAXSTMT
- Example