6.25.2 Estimating the number of pages in the buffer for local work tables

This section explains how to estimate the number of pages in the buffer for local work tables. A local work table is a work table specific to each real thread and is created for each real thread.

Once you have determined the number of pages in the buffer for local work tables, specify a value that is equal to it or greater in the adb_dbbuff_wrktbl_clt_blk_num operand in the server definition. For details, see the explanation of the adb_dbbuff_wrktbl_clt_blk_num operand in 7.2.2 Operands related to performance (set format).

As needed, also specify the following operand and option:

adb_dbbuff_wrktbl_clt_blk_num operand in the client definition

See Operands related to performance in the HADB Application Development Guide.
Export option adb_export_wrktbl_blk_num

See Specification format for the adbexport command under adbexport (Export Data) in the manual HADB Command Reference.

The formula for determining the number of pages in the buffer for local work tables follows.

Formula (pages)

[Figure]

Explanation of variables

A: Number of work tables created when executing an SQL statement × 2

B: Value of the adb_sys_uthd_num operand in the server definition

Determine the number of work tables that are created when SQL statements are executed, see 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. Note the following when determining the number of work tables:

Determine the number of work tables to be created for each SQL statement based on the explanation in the example explained in Number of work tables that are created in the HADB Application Development Guide. From the resulting values, use the largest value for calculating the number of tables.

If there is a subquery that includes an external reference column, determine the number of work tables to be created based on the explanation in the example explained in Number of work tables that are created in the HADB Application Development Guide. If the resulting value is designated S, use the following formula to calculate the number of work tables that will be created ultimately.

Formula (work tables)

[Figure]

Explanation of variables

T: Value of the adb_sys_uthd_num operand in the server definition

U: Number of rows in the result before the evaluation of a subquery of a query specification that searches for columns that are referenced as external reference columns

When executing a retrieval using a hash table, add the value determined from the following formula to the number of work tables to be created for executing SQL statements:

For details about how to determine the number of work tables created during retrieval using hash tables, see 6.25.3 Number of work tables created during retrieval using hash tables.

The following is a calculation example of the number of work tables to be created when executing an SQL statement:

Example

This example determines the number of work tables to be created when executing the SQL statement shown.

SELECT "C1","C2","C3" FROM "T1"
  WHERE "C1"=ANY(                                         ...1
                SELECT COUNT(DISTINCT "C3") FROM "T2"
                  WHERE "T1"."C1"="C2" GROUP BY "C1")     ...2
                  ORDER BY "C1","C2","C3"                 ...3

Explanation

Creates a work table for a quantified predicate. However, because this is a subquery that includes an external reference column ("T1"."C1"), use the following formula to determine the number of work tables to be created:

[Figure]

Creates a work table to be used for sort processing by the GROUP BY clause. However, because this is a subquery that includes an external reference column ("T1"."C1"), use the following formula to determine the number of work tables to be created:

[Figure]

Creates a work table to be used for sort processing by the ORDER BY clause. In this case, a single work table is created.

The number of work tables to be created when executing the above SQL statement is the combined total of steps 1 through 3.

To Page Top