Browse Prior Art Database

Improving the Performance of Correlated Subqueries in SQL

IP.com Disclosure Number: IPCOM000102847D
Original Publication Date: 1990-Feb-01
Included in the Prior Art Database: 2005-Mar-17
Document File: 1 page(s) / 50K

Publishing Venue

IBM

Related People

Couch, BT: AUTHOR [+2]

Abstract

An algorithm is disclosed whereby correlated subqueries that return a single value can be evaluated more efficiently by eliminating re-executions of the subquery.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 72% of the total text.

Improving the Performance of Correlated Subqueries in SQL

      An algorithm is disclosed whereby correlated subqueries that
return a single value can be evaluated more efficiently by
eliminating re-executions of the subquery.

      A correlated subquery can be viewed as a parameterized
subquery, whereby the correlated references are the input parameters
to the subquery.  The result of the correlated subquery depends upon
those input parameters (correlated references) to the subquery.  Each
row of the outer query produces new parameters as input to the
subquery, requiring it to be re-executed.  However, whenever those
parameters are identical, the subquery produces the same result.  To
save re-executions of the subquery, the subquery results can be saved
so that they can be reused.

      After execution of the first subquery, its result and the
values of the correlated references are saved in a table in memory.
The number of results and corresponding reference values is limited
to some maximum number to keep the table search efficient.

      For each row of the outer query, the saved correlated reference
values are searched (starting with the most recent) for a match with
the new reference values.  If a match is found, the corresponding
subquery result is reused and the subquery evaluation is skipped,
thus saving the re-execution of the subquery.  If a match is not
found, the subquery is evaluated and the new result and corresponding
reference value...