Cached Views

• US 20150347410A1
• Filed: 06/03/2014
• Published: 12/03/2015
• Est. Priority Date: 06/03/2014
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Kim, Ki Hong, May, Norman, Boehm, Alexander, Wi, Sung Heun, Han, Jeong Ae, Song, Sang Il, Yoon, Yongsik
• Granted Patent: US 10,061,808 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

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.

Claims

1. A computer-implemented method comprising:

causing an engine to receive a query including parameters;

causing the engine to determine if the parameters match cache-defined columns;

if a parameter does not match a cache-defined column, returning a query result satisfied by a view from an underlying database;

if the parameters match the cache-defined columns, causing the engine to determine if an existing cache instance satisfies the query;

if a cache instance does not satisfy the query, causing the engine to create a new cache instance with a view from the underlying database, and returning the query result with the new cache instance;

if the existing cache instance satisfies the query, causing the engine to return the query result with the existing cache instance.

2. A method as in claim 1 wherein the existing cache instance satisfies the query when a same grouping is used.

3. A method as in claim 2 wherein the existing cache instance satisfies the query when a same explicit group-by clause is used.

4. A method as in claim 2 wherein the existing cache instance satisfies the query when a same requested column list is used.

5. A method as in claim 1 wherein the new cache instance is created for each tuple of literals for a plurality of cache-defined columns.

6. A method as in claim 1 further comprising evicting the existing cache instance after a period of time.

7. A method as in claim 6 wherein the evicting is based upon a Least Recently Used (LRU) strategy.

8. A non-transitory computer readable storage medium embodying a computer program for performing a method, said method comprising:

causing an engine to receive a query including parameters;

causing the engine to determine if the parameters match cache-defined columns;

if a parameter does not match a cache-defined column, returning a query result satisfied by a view from an underlying database;

if the parameters match the cache-defined columns, causing the engine to determine if an existing cache instance satisfies the query;

if a cache instance does not satisfy the query, causing the engine to create a new cache instance with a view from the underlying database, and returning the query result with the new cache instance;

if the existing cache instance satisfies the query, causing the engine to return the query result with the existing cache instance.

9. A non-transitory computer readable storage medium as in claim 8 wherein the existing cache instance satisfies the query when a same grouping is used.

10. A non-transitory computer readable storage medium as in claim 9 wherein the existing cache instance satisfies the query when a same explicit group-by clause is used.

11. A non-transitory computer readable storage medium as in claim 9 wherein the existing cache instance satisfies the query when a same requested column list is used.

12. A non-transitory computer readable storage medium as in claim 8 wherein the new cache instance is created for each tuple of literals for a plurality of cache-defined columns.

13. A non-transitory computer readable storage medium as in claim 8 further comprising evicting the existing cache instance after a period of time.

14. A non-transitory computer readable storage medium as in claim 13 wherein the evicting is based upon a Least Recently Used (LRU) strategy.

15. A computer system comprising:

one or more processors;

a software program, executable on said computer system, the software program configured to;

cause an engine to receive a query including parameters;

cause the engine to determine if the parameters match cache-defined columns;

if a parameter does not match a cache-defined column, return a query result satisfied by a view from an underlying database;

if the parameters match the cache-defined columns, cause the engine to determine if an existing cache instance satisfies the query;

if a cache instance does not satisfy the query, cause the engine to create a new cache instance with a view from the underlying database, and return the query result with the new cache instance;

if the existing cache instance satisfies the query, cause the engine to return the query result with the existing cache instance.

16. A computer system as in claim 15 wherein the existing cache instance satisfies the query when a same grouping is used.

17. A computer system as in claim 16 wherein the existing cache instance satisfies the query when a same explicit group-by clause is used.

18. A computer system as in claim 16 wherein the existing cache instance satisfies the query when a same requested column list is used.

19. A computer system as in claim 15 wherein the new cache instance is created for each tuple of literals for a plurality of cache-defined columns.

20. A computer system as in claim 15 further comprising evicting the existing cache instance after a period of time.