This topic shows how to measure the cost of a Derby-style table function.

The following formula describes how to estimate the value returned
by *VTICosting.getEstimatedCostPerInstantiation()*:

**
C = I * A
**

where

**C**= The estimated**C**ost for creating and running the table function. That is, the value returned by*VTICosting.getEstimatedCostPerInstantiation()*. In general,**C**ost is a measure of time in milliseconds.**I**= The optimizer's**I**mprecision. A measure of how skewed the optimizer's estimates tend to be in your particular environment. See below for instructions on how to estimate this**I**mprecision.**A**= The**A**ctual time in milliseconds which it takes to create and run this table function.

We treat optimizer **I**mprecision as a constant across the
runtime environment. The following formula describes it:

**
I = O / T
**

where

**O**= The**O**ptimizer's estimated cost for a plan.**T**= The**T**otal runtime in milliseconds for the plan.

To estimate these values, turn on Derby statistics collection and run the following experiment several times, averaging the results:

**Select**= Select all of the rows from a big table.**Record**= In the statistics output, look for the ResultSet which represents the table scan. That scan has a field labelled "optimizer estimated cost". That's**O**. Now look for the fields in that ResultSet's statistics labelled "constructor time", "open time", "next time", and "close time". Add up all of those fields. That total is**T**.

For example:

MAXIMUMDISPLAYWIDTH 7000; CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1); select * from T; values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

The following formula explains how to compute the **A**ctual
runtime cost for the table function:

**
A = ( P * N ) + E
**

where

**P**= The runtime spent**P**er row (in milliseconds).**N**= The**N**umber of rows in the table function.**E**= The time spent creating an**E**mpty instance of the table function which has no rows in it. Usually,**P * N**dwarfs**E**. That is, the table function instantiation cost is very small compared to the actual cost of looping through the rows. However, for some table functions,**E**may be significant and may dominate the table function's cost when**N**is small.

You may know that **E** is basically 0. If so, you can skip this step.
Otherwise, to estimate **E**, turn on
Derby statistics collection
and run the following experiment several times, averaging the results:

**Short-circuit**= Short-circuit the next() method of the*ResultSet*returned by your Derby-style table function so that it returns*false*the first time it is called. This makes it appear that the*ResultSet*has no rows.**Select**= Select all of the rows from the table function.**Record**= In the statistics output, look for the VTIResultSet which represents the table function scan. Add up the values of the fields in that VTIResultSet's statistics labelled "constructor time", "open time", "next time", and "close time". That total is**E**.

To estimate **P**, turn on
Derby statistics collection
and run the following experiment several times, averaging the results:

**Select**= Select all of the rows from the table function.**Record**= In the statistics output, look for the VTIResultSet which represents the table function scan. Add up the values of the fields in that VTIResultSet's statistics labelled "constructor time", "open time", "next time", and "close time". Subtract**E**from the result. Now divide by the value of the field "Rows seen". The result is**P**.

Putting all of this together, the following formula describes the
value returned by your
table function's
*VTICosting.getEstimatedCostPerInstantiation()*
method.

**
C = O/T * [ ( P * N ) + E ]
**