Embodiments relate to view caching techniques that cache for a limited time, some of the (intermediate) results of a previous query execution, in order to avoid expensive re-computation of query results. Particular embodiments may utilize a cache manager to determine whether information relevant to a subsequent user request can be satisfied by an existing cache instance or view, or whether creation of an additional cache instance is appropriate. At design time, cache defining columns of a view are defined, with user input parameters automatically being cache defining. Cache instances are created for each tuple of literals for the cache defining columns, and for each explicit or implicit group by clause. Certain embodiments may feature enhanced reuse between cache instances, in order to limit memory footprint. Over time a cache instances may be evicted from memory based upon implementation of a policy such as a Least Recently Used (LRU) strategy.
1. A method comprising:
an engine of an in-memory database storing in the in-memory database, a cache view comprising an existing cache instance including a plurality of cache defined columns and a grouping;
the engine receiving a query including parameters comprising a MEDIAN aggregation function that is not composable;
the engine determining when the parameters match all of the cache-defined columns and the grouping;
when a parameter does not match all of the cache-defined columns and the grouping, returning a query result satisfied by a view from an underlying database;
when the parameters match all of the cache-defined columns and the grouping, the engine determining when the existing cache instance satisfies the query;
when the existing cache instance does not satisfy the query, the engine creating a new cache instance with a view from the underlying database to add to the cache view, and returning the query result with the new cache instance, wherein the new cache instance is created for each tuple of literals for all of the cache-defined columns, and wherein the new cache instance is grown incrementally from another query utilizing an UPSERT function;
when the existing cache instance satisfies the query and the cache-defined columns are equal, the engine returning the query result with the existing cache instance, wherein the existing cache instance satisfies the query when a same explicit group-by clause is used for the grouping.
2. The method as in claim 1 wherein the existing cache instance satisfies the query when a same requested column list is used for the grouping.
3. The method as in claim 1 further comprising evicting the existing cache instance after a period of time.
4. The method as in claim 3 wherein the evicting is based upon a Least Recently Used (LRU) strategy.
5. The method as in claim 1 further comprising:
the engine calculating a cost factor of creating the new cache instance; and
the engine considering the cost factor in deciding to create the new cache instance.
6. A non-transitory computer readable storage medium embodying a computer program for performing a method, said method comprising:
an engine of an in-memory database storing in the in-memory database, a cache view comprising an existing cache instance including a plurality of cache defined columns and a grouping;
the engine receiving a query including parameters comprising a MEDIAN aggregation function that is not composable;
the engine determining when the parameters match all of the cache-defined columns and the grouping;
when a parameter does not match all of the cache-defined columns and the grouping, returning a query result satisfied by a view from an underlying database;
when the parameters match all of the cache-defined columns and the grouping, the engine determining when the existing cache instance satisfies the query;
when the existing cache instance does not satisfy the query, the engine creating a new cache instance with a view from the underlying database to add to the cache view, and returning the query result with the new cache instance, wherein the new cache instance is created for each tuple of literals for all of the cache-defined columns, and wherein the new cache instance is grown incrementally from another query utilizing an UPSERT function;
when the existing cache instance satisfies the query and the cache-defined columns are equal, the engine returning the query result with the existing cache instance, wherein the existing cache instance satisfies the query when a same explicit group-by clause is used for the grouping.
7. The non-transitory computer readable storage medium as in claim 6 wherein the existing cache instance satisfies the query when a same requested column list is used for the grouping.
8. The non-transitory computer readable storage medium as in claim 6 further comprising evicting the existing cache instance after a period of time.
9. The non-transitory computer readable storage medium as in claim 8 wherein the evicting is based upon a Least Recently Used (LRU) strategy.
10. The non-transitory computer readable storage medium as in claim 6 further comprising:
the engine calculating a cost factor of creating the new cache instance; and
the engine considering the cost factor in deciding to create the new cache instance.
11. A computer system comprising:
one or more processors;
a software program, executable on said computer system, the software program configured to instruct an engine of an in-memory database to:
store in the in-memory database, a cache view comprising an existing cache instance including a plurality of cache defined columns and a grouping;
receive a query including parameters comprising a MEDIAN aggregation function that is not composable;
determine when the parameters match all of the cache-defined columns and the grouping;
when a parameter does not match all of the cache-defined columns and the grouping, return a query result satisfied by a view from an underlying database;
when the parameters match all of the cache-defined columns and the grouping, the engine determining when an existing cache instance satisfies the query;
when the existing cache instance does not satisfy the query, the engine creating a new cache instance with a view from the underlying database to add to the cache view, and return the query result with the new cache instance, wherein the new cache instance is created for each tuple of literals for all of the cache-defined columns, and wherein the new cache instance is grown incrementally from another query utilizing an UPSERT function;
when the existing cache instance satisfies the query and the cache defining columns are equal, the engine returning the query result with the existing cache instance, wherein the existing cache instance satisfies the query when a same explicit group-by clause is used for the grouping.
12. The computer system as in claim 11 further comprising evicting the existing cache instance after a period of time.
13. The computer system as in claim 11 further comprising:
the engine calculating a cost factor of creating the new cache instance; and
the engine considering the cost factor in deciding to create the new cache instance.
14. The computer system as in claim 11 wherein the existing cache instance satisfies the query when a same requested column list is used for the grouping.