User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 25. Query Optimization
New to Adaptive Server Anywhere 6.0 is the ability to cache the result of evaluating a subquery. When Anywhere processes a subquery, it caches the result. Should it need to re-evaluate the subquery for the same value, it can simply be retrieve the result from the cache. In this way, Anywhere can avoid many repetitious and redundant computations.
At the end of each subquery, Anywhere releases the stored values. Since values may change between queries, these values may not be reused to process subsequent queries. For example, another transaction might modify values in a table involved in the subquery.
As the processing of a query progresses, Anywhere monitors the frequency with which cached subquery values are reused. If the values of the correlated variable rarely repeat, then Anywhere needs to compute most values only once. In this situation, Anywhere recognizes that it is more efficient to recompute occasional duplicate values, than to cache numerous entries that occur only once.
Anywhere also does not cache if the size of the dependent column is more than 255 bytes. In such cases, you may wish to rewrite your query or add another column to your table to make such operations more efficient.
As soon as Adaptive Server Anywhere recognizes that few values are repeated, it suspends subquery caching for the remainder of the statement and proceeds to re-evaluate the subquery for each and every row in the outer query block.