13.1.6 Reducing the SQL statement processing time

(1) How to reduce the SELECT statement's processing time

The following procedure explains how to reduce the SELECT statement's processing time.

Procedure

1. Check the execution time of the SELECT statement.

   Check the following item in the connection operation information that is output when the adbstat command is executed:

   • SELECT_total_time (SELECT statement execution time)

2. Revise the value specified for the adb_sql_exe_max_rthd_num operand.

   If the SELECT_total_time value is large, processing of SQL statements might be taking too long. In such a case, increasing the value specified for the adb_sql_exe_max_rthd_num operand in the server definition and client definition might improve processing performance in some cases.

   Important

   • Check the values of both the adb_sql_exe_max_rthd_num operand in the client definition and the adb_sql_exe_max_rthd_num operand in the server definition. If the value specified in the client definition exceeds the value specified in the server definition as a result of increasing the value in the client definition only, the value specified in the server definition is applied. The new value specified in the client definition is not applied. If this case applies, increase the values of the adb_sql_exe_max_rthd_num operands in both the server definition and client definition.

   • If you increase the value of the adb_sql_exe_max_rthd_num operand, increase also the value of the adb_sys_rthd_num operand in the server definition. If you increase only the value specified for the adb_sql_exe_max_rthd_num operand, SQL statement execution wait status might result.

   • When you have changed the values of the adb_sql_exe_max_rthd_num and adb_sys_rthd_num operands, always re-estimate the HADB server's memory requirement. For details about estimation of the HADB server's memory requirement, see 6.3 Estimating the HADB server's memory requirement.

3. Terminate the HADB server.

   Terminate the HADB server by executing the adbstop command.

4. Change the server definition and client definition.

   Increase the values of the following operands:

   • The adb_sql_exe_max_rthd_num operands in the server definition and client definition

   • The adb_sys_rthd_num operand in the server definition

5. Start the HADB server.

   Execute the adbstart command to start the HADB server.

Note

• For details about the adb_sql_exe_max_rthd_num and adb_sys_rthd_num operands in the server definition, see the adb_sql_exe_max_rthd_num and adb_sys_rthd_num operands in 7.2.2 Operands related to performance (set format).

• For details about the adb_sql_exe_max_rthd_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

To Page Top

(2) How to reduce the processing time of definition SQL statements and data manipulation SQL statements

This subsection explains how to reduce the processing time of definition SQL statements and data manipulation SQL statements.

The methods provided here apply to the following SQL statements:

Definition SQL statements

• ALTER TABLE statement (when changing an archivable multi-chunk table to a regular multi-chunk table)

• DROP TABLE statement

• DROP INDEX statement

• DROP USER statement

• DROP SCHEMA statement

• REVOKE statement (revoking schema operation privileges)

Data manipulation SQLs

• PURGE CHUNK statement

• TRUNCATE TABLE statement

The following procedure explains how to reduce the processing time of definition SQL statements and data manipulation SQL statements.

Procedure

1. Check the number of DB area files.

   In the DB area summary information that is output when the adbdbstatus command is executed, check the following information:

   • Number_of_files (number of DB area files)

   Check the DB areas that store the tables and indexes subject to SQL statement processing. Identify the DB area containing the largest number of DB area files.

2. Revise the value specified for the adb_sql_exe_max_rthd_num operand.

   Compare the largest number of DB area files making up a DB area as displayed under Number_of_files in step 1 with the value of the adb_sql_exe_max_rthd_num operand. If the number of DB area files is larger than the operand's value, SQL statement processing time might be taking too long. If this is the case, increase the value of the adb_sql_exe_max_rthd_num operand so that it matches the number of DB area files. Processing performance might improve.

   Important

   • Check the values of both the adb_sql_exe_max_rthd_num operand in the client definition and the adb_sql_exe_max_rthd_num operand in the server definition. If the value specified in the client definition exceeds the value specified in the server definition as a result of increasing the value in the client definition only, the value specified in the server definition is applied. The new value specified in the client definition is not applied. If this case applies, increase the values of the adb_sql_exe_max_rthd_num operands in both the server definition and client definition.

   • If you increase the value of the adb_sql_exe_max_rthd_num operand, increase also the value of the adb_sys_rthd_num operand in the server definition. Unless you do so, SQL statement execution wait status might result.

   • When you have changed the values of the adb_sql_exe_max_rthd_num and adb_sys_rthd_num operands, always re-estimate the HADB server's memory requirement. For details about estimation of the HADB server's memory requirement, see 6.3 Estimating the HADB server's memory requirement.

3. Terminate the HADB server.

   Terminate the HADB server by executing the adbstop command.

4. Change the server definition and client definition.

   Increase the values of the following operands:

   • The adb_sql_exe_max_rthd_num operands in the server definition and client definition

   • The adb_sys_rthd_num operand in the server definition

5. Start the HADB server.

   Execute the adbstart command to start the HADB server.

Note

• For details about the adb_sql_exe_max_rthd_num and adb_sys_rthd_num operands in the server definition, see the adb_sql_exe_max_rthd_num and adb_sys_rthd_num operands in 7.2.2 Operands related to performance (set format).

• For details about the adb_sql_exe_max_rthd_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

To Page Top