Calculating cost

About this task

The cost you specify for a function must be compatible with the cost that the optimizer calculates for other parts of the SQL statement. The following formula is one method to approximate the costing algorithm that the optimizer uses:

Procedure

  1. Execute the following SQL statements from DB-Access, where bigtable is any large table:
    SET EXPLAIN ON;
SELECT count(*) from bigtable;

    Time the query.

  2. Let secost be the cost the optimizer assigned for the scan.
    Read the sqexplain.out file to get secost.

    For information about sqexplain.out, refer to the HCL OneDB™ Performance Guide.

  3. Let satime be the time required to complete the SQL statement.
  4. Execute and time your function.
    Let facost be the actual time required to execute the function once.
    The cost of executing the function once can be approximated as follows: 
    ((secost/satime)*facost)

    Truncate the calculated cost to an integer value.