13.2.4 Reducing the execution time of an SQL statement that creates a local work table

When you execute an SQL statement that creates a local work table, you can sometimes reduce its execution time by increasing the number of pages in the buffer for local work tables.

For details about SQL statements that create local work tables, see Work tables created when SQL statements are executed under Considerations when executing an SQL statement that creates work tables in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide.

The tuning method to use depends on the following information, output by the adbstat command:

Connection operation information
SQL statement statistical information

First, check the connection operation information and perform tuning. Afterward, if you want to reduce the execution time of a specific SQL statement, check the SQL statement's statistical information and perform tuning again.

Note: If the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition is not specified for a connection, you can use the adbmodbuff command while the HADB server is running to change the number of pages in local work table buffers that was specified in the adb_dbbuff_wrktbl_clt_blk_num operand in the server definition. See 11.12.1 Changing the local work table buffer.

Organization of this subsection

(1) Using the connection operation information to reduce the execution time of an SQL statement that creates a local work table
(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that creates a local work table

(1) Using the connection operation information to reduce the execution time of an SQL statement that creates a local work table

Procedure

Check the number of times files are written from the buffer for local work tables.

In the connection operation information that is output when the adbstat command is executed, check the following information:
- DBbuff_wrktbl_clt_write_cnt (number of times files are written from the buffer for local work tables)
Change the client definition.

If the value that is output to DBbuff_wrktbl_clt_write_cnt, which you checked in step 1, is at least 1, files have been written from the buffer for local work tables. Increase the value specified for the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition.

When you change the client definition, follow the procedure explained in Notes about changing a client definition in the HADB Application Development Guide.
Re-execute the SQL statement that creates a local work table.

After re-executing the SQL statement that creates a local work table, perform step 1 to check whether the value that was output to DBbuff_wrktbl_clt_write_cnt has decreased.
Repeat steps 1 to 3.

If you make adjustments by repeating steps 1 to 3 until the value that is output to DBbuff_wrktbl_clt_write_cnt becomes 0, you can sometimes reduce the execution time of the SQL statement that creates a local work table.

Note: For details about the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

To Page Top

(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that creates a local work table

Procedure

Check the number of times files are written from the local work table buffer.

Check the values of the following items in the SQL statement statistical information that was output when the adbstat command was executed:
- DBbuff_wrktbl_clt_write_cnt (number of files written from the local work table buffer)
- DBbuff_wrktbl_clt_put_cnt (number of files written to the local work table buffer)
Change the client definition.

If the relationship between the DBbuff_wrktbl_clt_write_cnt value and DBbuff_wrktbl_clt_put_cnt value, which you checked in step 1, satisfies the following formula, it is possible that files are being written from the local work table buffer frequently.

Formula
```
value output to DBbuff_wrktbl_clt_write_cnt > value output to DBbuff_wrktbl_clt_put_cnt ÷ 2
```
If the preceding formula is satisfied, increase the value specified for the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition.

When you change the client definition, follow the procedure explained in Notes about changing a client definition in the HADB Application Development Guide.
Re-execute the SQL statement that creates a local work table.

After re-executing the SQL statement that creates a local work table, perform step 1 to confirm that the value that was output to DBbuff_wrktbl_clt_write_cnt does not satisfy the formula.
Repeat steps 1 to 3.

If you make adjustments by repeating steps 1 to 3 until the value that is output to DBbuff_wrktbl_clt_write_cnt no longer satisfies the formula, you can sometimes reduce the execution time of the SQL statement that creates a local work table.

Note: For details about the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

To Page Top