Computing the Benefits of Join Indexes
This topic describes a measure you can use to calculate the benefit of a join index.
Begin the calculation by computing some preliminary measures, as described in the following set of equations.
Define the benefit of a join index as follows:
where:
|
Syntax element … |
Specifies elapsed query time … |
|
ETwo |
without a join index defined for the query. |
|
ETw |
with a join index defined for the query. |
Benefit is a simple measure that measures the advantage of a query in terms of the difference between its elapsed completion time when a join index is not defined and its elapsed completion time when a join index is defined.
Define the benefit percentage as follows:
The benefit percentage for a query is just a normalized form of the raw benefit. It provides an easily understood measure of the reduction in processing time gained by creating the join index.
Rearrange terms to determine the elapsed time to process a query with the join index defined. This measure is necessary to determine if the cost of creating the join index exceeds its usefulness in reducing response time.
