ASA SQL User's Guide
Query Optimization and Execution
When Adaptive Server Anywhere processes a subquery, it caches the result. This caching is done on a request-by-request basis; cached results are never shared by concurrent requests or connections. Should Adaptive Server Anywhere need to re-evaluate the subquery for the same set of correlation values, it can simply retrieve the result from the cache. In this way, Adaptive Server Anywhere avoids many repetitious and redundant computations. When the request is completed (the query's cursor is closed), Adaptive Server Anywhere releases the cached values.
As the processing of a query progresses, Adaptive Server Anywhere monitors the frequency with which cached subquery values are reused. If the values of the correlated variable rarely repeat, then Adaptive Server Anywhere needs to compute most values only once. In this situation, Adaptive Server Anywhere recognizes that it is more efficient to recompute occasional duplicate values, than to cache numerous entries that occur only once. Hence the server suspends the caching of this subquery for the remainder of the statement and proceeds to re-evaluate the subquery for each and every row in the outer query block.
Adaptive Server 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.
Some built-in and user-defined functions are cached in the same way that subquery results are cached. This can result in a substantial improvement for expensive functions that are called during query processing with the same parameters. However, it may mean that a function is called less times than would otherwise be expected.
For a functions to be cached, it must satisfy two conditions:
It must always return the same result for a given set of parameters.
It must have no side effects on the underlying data.
Functions that satisfy these conditions are called deterministic or idempotent functions.
Built-in functions are treated as deterministic with a few exceptions. The RAND, NEW_ID, and GET_IDENTITY functions are treated as non-deterministic, and their results are not cached.
User-defined functions are treated as deterministic unless they are specified as NOT DETERMINISTIC when created.
For more information about user-defined functions, see CREATE FUNCTION statement.