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.

Description:
BACKGROUND 
       [0001]    Embodiments relate to accessing data from a database, and in particular, to methods and systems providing cached views. 
         [0002]    Unless otherwise indicated herein, the approaches described in this section are not prior art to the claims in this application and are not admitted to be prior art by inclusion in this section. 
         [0003]    Databases have evolved to afford both increased capacity and complexity of the data structures that may be stored. As a result, customers can run complex queries on database tables from the application layer, without changing the underlying data model. 
         [0004]    Instead, databases such as in memory database architectures may rely upon complex models that join, project, and aggregate the existing tables using relational operators in order to provide insights to the business user. These models and resulting database queries may implicate up to hundreds of database tables, and produce very large intermediate results in the realm of even billions of records. 
         [0005]    Depending on the usage pattern of an overlying application building upon such a database, such complex queries can be run in a high frequency, producing a large query workload for the underlying database management system. In order to reduce this workload, and avoid expensive query re-computation, some database management systems may employ caching techniques. With the advent of more powerful database technology, however, such traditional caching techniques may still result in the database management system being forced to handle large data volumes. 
         [0006]    Accordingly, there is a need for apparatuses and methods that performing improved caching approaches. 
       SUMMARY 
       [0007]    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. 
         [0008]    An embodiment of a computer-implemented method comprises causing an engine to receive a query including parameters. The engine is caused to determine if the parameters match cache-defined columns If a parameter does not match a cache-defined column, a query result is returned satisfied by a view from an underlying database. If the parameters match the cache-defined columns, the engine is caused 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. 
         [0009]    A non-transitory computer readable storage medium embodies a computer program for performing a method comprising causing an engine to receive a query including parameters, and causing the engine to determine if the parameters match cache-defined columns. If a parameter does not match a cache-defined column, a query result is returned satisfied by a view from an underlying database. If the parameters match the cache-defined columns, the engine determines if an existing cache instance satisfies the query. If a cache instance does not satisfy the query, the engine is caused 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, the engine is caused to return the query result with the existing cache instance. 
         [0010]    An embodiment of a computer system comprises one or more processors and a software program, executable on said computer system. The software program is configured to cause an engine to receive a query including parameters, and to cause the engine to determine if the parameters match cache-defined columns If a parameter does not match a cache-defined column, a query result is returned satisfied by a view from an underlying database. If the parameters match the cache-defined columns, the engine is caused to determine if an existing cache instance satisfies the query. If a cache instance does not satisfy the query, the engine is caused 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, the engine is caused to return the query result with the existing cache instance. 
         [0011]    In certain embodiments the existing cache instance satisfies the query when a same grouping is used. 
         [0012]    According to some embodiments the existing cache instance satisfies the query when a same explicit group-by clause is used. 
         [0013]    In various embodiments the existing cache instance satisfies the query when a same requested column list is used. 
         [0014]    In particular embodiments the new cache instance is created for each tuple of literals for a plurality of cache-defined columns 
         [0015]    Certain embodiments may further comprise evicting the existing cache instance after a period of time. 
         [0016]    In some embodiments the evicting may be based upon a Least Recently Used (LRU) strategy. 
         [0017]    The following detailed description and accompanying drawings provide a better understanding of the nature and advantages of embodiments. 
     
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         [0018]      FIG. 1  illustrates a system configured to perform view caching according to one embodiment. 
           [0019]      FIGS. 2A-2G  illustrate one embodiment of a method of view caching. 
           [0020]      FIG. 3  is a simplified flow diagram illustrating steps of a method of view caching according to an embodiment. 
           [0021]      FIG. 4  shows a specific example of the implementation of view caching according to an embodiment. 
           [0022]      FIG. 5  illustrates hardware of a special purpose computing machine configured to implement view caching according to an embodiment. 
           [0023]      FIG. 6  illustrates an example of a computer system. 
       
    
    
     DETAILED DESCRIPTION 
       [0024]    Described herein are techniques for performing view caching according to an embodiment. In the following description, for purposes of explanation, numerous examples and specific details are set forth in order to provide a thorough understanding of the present invention. It will be evident, however, to one skilled in the art that the present invention as defined by the claims may include some or all of the features in these examples alone or in combination with other features described below, and may further include modifications and equivalents of the features and concepts described herein. 
         [0025]    Embodiments employ materialized views to store the result of a particular query, for example as a temporary table. When a query including a materialized view as a subset of the query graph is received, the manager accesses the materialized view instead of re-computing the result of the query graph subset on the fly, thereby reducing computation effort. 
         [0026]    Particular embodiments allow definition at design time, of a set of parameters (cache-defining columns) for which data should be cached. Under certain circumstances, an amount of data being materialized may be dynamically grown. As described below, for some parameter combinations not matching cache-defining columns, the materialized view may be ignored without being stored in the cache. 
         [0027]    Utilization of cached views according to embodiments, leverages the lack of suitability of some materialized views for use with complex databases and searching. For example in some cases the result view, and/or the complexity of a view model, disallows materializing a sub-query without additional restrictions on predicates (e.g., client=500 and area=&#39;Korea&#39;). 
         [0028]    Those parameters, however, are not known at the time of query compilation (when the query is matched with the materialized view). Thus, such a restriction would disallow use of the materialized view, or require multiple alternative plans to be stored. 
         [0029]    Moreover, deferring the decision to rely upon materialized views until query runtime, is also not easily achievable. This is because a query could potentially make use of a large number of extant materialized views, thereby adding a high number of choices and complexity to the query execution time. 
         [0030]    Accordingly, embodiments employing the cached view approach avoids these issues by relying upon a single execution plan involving multiple cached views (also referred to herein as cache instances), and a set of (predefined and/or incrementally growing) view-specific parameters defining the content of a cached view. Thus whenever a query potentially making use of a cached view is being executed, a corresponding cache manager component checks whether the query parameters (e.g. cache-defining columns) match an existing entry in the cache. Then, either the existing entry is used as a query result, or the corresponding subquery is evaluated dynamically. 
         [0031]    Depending on the policy of the cached view, this new result may be stored for a subsequent query as part of the cached view, thereby dynamically growing the amount of data that is being materialized. Under some parameter conditions (e.g., where the query parameter does not match the cache-defined column), the new result may be ignored and discarded without being stored in the cache. 
         [0032]      FIG. 1  shows a simplified view of a system configured to implement view caching according to an embodiment. The system  100  comprises an application layer  102  overlying a database layer  104 . One example of a database layer is the HANA in-memory database available from SAP AG of Walldorf, Germany. An example of an overlying application is the ABAP platform also available from SAP. 
         [0033]    The database layer includes a database  106  stored in a non-transitory computer readable storage medium  108 . The database layer further comprises a database engine  110  that is configured to govern the movement of database data to and from the database. An example of such database data is that contained in a view  112  that is responsive to queries issued by the application layer to the database layer. 
         [0034]    As described herein, under certain circumstances data available in such a view may be stored in a cache  114  for later reuse. Such reuse improves performance by reducing the processing burden incurred by having to request all data from the database directly. 
         [0035]    In particular, the application layer also comprises an engine  116  that is configured to manage operation of the cache, including the creation of specific cache instances  118 . This engine  116  is also referred to herein as a cache manager. 
         [0036]    While the particular embodiment of  FIG. 1  places the cache  114  and the cache manager  116  inside the application layer, this is not required. According to various embodiments the cache and/or cache manager could be implemented in the application layer or in the database layer. 
         [0037]    In certain embodiments, the cache and/or cache manager could be located in an in-memory database. Processing power available to an in-memory database could be harnessed to perform caching according to various embodiments. 
         [0038]    Examples of such in memory databases include the HANA database just mentioned, as well as the SYBASE IQ database also available from SAP AG; the Microsoft Embedded SQL for C (ESQL/C) database available from Microsoft Corp. of Redmond, Wash.; and the Exalytics In-Memory database available from Oracle Corp. of Redwood Shores, Calif. 
         [0039]    Operation of the cache manager is now described in connection with a consumer  120  of the database data, issuing a request (query)  121  for information from the database. As described below, this query  121  may comprise one or more elements (parameters)  122  that define with specificity the information that is sought from the database. 
         [0040]    The cache manager receives this query. The cache manager examines the query structure to determine whether it can be satisfied from an existing cache instance. 
         [0041]    Three possible system reactions are possible. A first system outcome is to satisfy the latest request (query) from an existing cache instance. Criteria for such view matching may be where the same values occur in cache defining columns that are specified at design time, and where the same grouping occurs. This may be an explicit group-by clause, or may be a requested column list. 
         [0042]    A second system outcome is to create a new cache instance, and to satisfy the latest request from this news cache instance. A third system outcome is to satisfy the latest request from the view retrieved from the database layer, and not build a new cache instance. 
         [0043]    Implementation of view caching according to one specific embodiment, is now illustrated and described in connection with  FIGS. 2A-2G . This embodiment relates to display of information retrieved using the HANA Studio software program available from SAP AG of Walldorf, Germany. 
         [0044]      FIG. 2A  shows implementation of view caching by a developer at design time. In particular, the developer defines which columns (c#) can be used to define the selection criteria (WHERE condition, input parameter) for the cache. Here, the particular View V 1  comprises a Join followed by a Projection. 
         [0045]    Also at design time, the developer may predefine cache lifetime properties. An example of such properties would be eviction from memory of specific cache instances based upon a Least Recently Used (LRU) strategy. 
         [0046]      FIGS. 2B-D  show the next steps taking place during runtime, with cache building occurring during consumption of the view. In particular,  FIG. 2B  shows Consumer  1  of the database data, running the following query against the View V 1 :
       Select c 1 ,c 2 ,c 3 ,c 4  from v 1     where c 1 =‘1’   and c 2 =‘2’   and c 3 =‘x’       
 
         [0051]    Now, the Cache Manager recognizes that all of cache defining columns (c 1 , c 2 ) are part of the where clause of this query. Because no suitable cache instance exists so far, the Cache Manager decides to build a cache. 
         [0052]      FIG. 2C  shows creation of the cache instance. In particular, this figure shows creation of the Cache Instance  1  as the result of the following operation:
       select c 1 ,c 2 ,c 3 ,c 4  from v 1 
           into cache_instance — 1   
           where c 1 =‘1’
           and c 2 =‘2’   
               
 
         [0057]      FIG. 2D  shows satisfaction of the consumer request from the cache. In particular the Cache Manager reaches into the cache to retrieve the existing intermediate result, rather than recomputing it on the fly. 
         [0058]      FIG. 2E  shows handing of a subsequent (different) query utilizing view caching. Here, the cache manager receives the following query to run against the same View V 1 :
       Select c 1 , c 2 , c 3 , c 4  from v 1     where c 1 =‘1’
           and c 2 =‘2’   and c 4 =‘C’   
               
 
         [0063]      FIG. 2E  shows that the where clause of this new query contains all of the cache defining columns (c 1 , c 2 ), with the same literals as in the existing cache instance. The requested column list is the same as the one for which the cache was built, and the new query can thus be satisfied from the cache. 
         [0064]      FIG. 2F  shows yet another possible query being run against the View V 1 :
       Select c 1 , c 4  from v 1     where c 1 =‘1’
           and c 4 =‘C’   
               
 
         [0068]    Here, the where clause of this query does not contain all of the cache defining columns (i.e., c 2  is not specified). Thus according to the view caching policy, the above query cannot be satisfied from the cache. The query will be satisfied by the view, and no new cache instance is built. 
         [0069]      FIG. 2G  shows still another subsequent query being run against the View V 1 :
       Select c 1 , c 4  from v 1     where c 1 =‘1’
           and c 2 =‘3’   and c 4 =‘C’   
               
 
         [0074]    The above where clause contains all of the cache defining columns (c 1 , c 2 ), but with literals that do not fit the existing cache. That is, the sole Cache Instance  1  does not include any record (row) having c 1 =1, c 2 =3, and c 4 =C. In addition, the requested column list is different for this query. 
         [0075]    Accordingly, the query cannot be satisfied from the sole existing cache instance of the cache. A new Cache Instance  2  is built from the following operation:
       select c 1 , c 4  from v 1 
           into cache_instance — 2   
           where c 1 =‘1’
           and c 2  =‘3’
 
 FIG. 2G  shows satisfaction of the latest query from this new cache instance.
   
               
 
         [0080]    View caching as described above, is now summarized in connection with the simplified flow diagram of  FIG. 3 . In particular, process  300  comprises a first step  302  in which a cache defining column of a view is determined at design time. 
         [0081]    In particular, cache defining columns may be chosen such that the resultant view cache can be used for as many queries as possible. If a query requires a column that is not a cache defining one, then the query cannot benefit from the view cache. 
         [0082]    For instance, consider a view cache that is created for columns a and b. If a query requires column a, b and c, that query cannot use the cache. 
         [0083]    However, if a number of cache defining columns is so large as to cover a large number of columns used in entire queries, the benefit of view cache may decrease in terms of cache memory and query performance. Consider that the cache defining columns are grouping columns Then, many grouping columns produce a large number of small groups, which results in a large memory consumption for caching. Thus according to particular embodiments, cache defining columns are likely to be grouping columns 
         [0084]    During runtime, in a second step  304  a cache manager receives a query comprising a parameter. In a third step  306 , the cache manager checks to see whether the parameter of the query matches the cache defining column. That is, the cache manager determines whether a literal of the query can be satisfied by a cache defining column at query execution time. 
         [0085]    In step  308 , if the parameter does not match the cache defining column, then the query is satisfied from a view obtained from the database. If, however, the parameter does match the cache-defining column, in step  310  the cache manager determines whether an existing cache instance satisfies the query. 
         [0086]    In step  312 , if the cache instance exists, it is used to provide a query result. No recourse to the database to obtain a view is required. 
         [0087]    If no cache instance exists, in step  314  a new cache instance is created. That new cache instance is then used to satisfy the query. 
       EXAMPLE 
       [0088]    A specific example of the implementation of view caching is now described in connection with  FIG. 4 . In particular,  FIG. 4  shows the cache comprising columns showing dimensions (characteristics) and columns showing measures (key figures). 
         [0089]    Step  1 . of  FIG. 4  shows filling in rows of the cache, for where Client=900 and Country=‘DE’, group by Client, Country, Period. Such cached information can be used for a query with: 1) less or equal grouping columns (depending on the aggregation function used); and 2) more or equal filters. A first example is where Client=900 and Country=‘DE’, group by Client, Country. A second example is where Client=900 and Country=‘DE’ and Period=201301, group by Client, Country. 
         [0090]    As mentioned above, the grouping columns depend upon the aggregation functions used. If they are not composable, then the grouping columns must be equal. As an example, MEDIAN is an aggregation function that is not composable, but SUM, MIN, COUNT are. 
         [0091]    Step  2 . shows filling in additional rows of the cache. Here, the cache is filled for where Client=900 and Country=‘KR’, group by Client, Country. 
         [0092]    Step  3 . shows still further filling in of additional rows of the cache. Here, the cache is filled for where Client=900, group by Client, Period. 
         [0093]    Step  4 . shows a situation of a posed query where Client=900 and Product=‘ERP’ and Period IN (201301, 201302, 201303), group by Client, Product, Period. A query is run to get the result shown. Then, add to the cached view. 
         [0094]    It is noted that this example also shows how incremental caching works. Such incremental caching can address the issue of having many cache defining columns, as mentioned above. 
         [0095]    Step  5 . shows a query with where Client = 900  group by Country. A query is run to get the result shown. Then, do UPSERT. 
         [0096]    It is noted that full SQL and HANA column views are covered for the queries. While in some embodiments view matching may be based on a name of the cached view. However embodiments could also work for an exact match of the matched view and with a limitation of more general matching. 
         [0097]    It is also noted that the decision to use the cache or not, may be based on a relative cost. That is, under certain circumstances a cost (e.g., processing) of creating a cache may outweigh a benefit of using it. Thus in certain embodiments a cost factor may be calculated and considered in deciding whether or not to create a cache/cache instance. 
         [0098]      FIG. 5  illustrates hardware of a special purpose computing machine configured to perform view caching according to an embodiment. In particular, computer system  501  comprises a processor  502  that is in electronic communication with a non-transitory computer-readable storage medium  503 . This computer-readable storage medium has stored thereon code  505  corresponding to a cache. Code  504  corresponds to a query manager engine. Code may be configured to reference data stored in a database of a non-transitory computer-readable storage medium, for example as may be present locally or in a remote database server. Software servers together may form a cluster or logical network of computer systems programmed with software programs that communicate with each other and work together in order to process requests. 
         [0099]    An example computer system  610  is illustrated in  FIG. 6 . Computer system  610  includes a bus  605  or other communication mechanism for communicating information, and a processor  601  coupled with bus  605  for processing information. Computer system  610  also includes a memory  602  coupled to bus  605  for storing information and instructions to be executed by processor  601 , including information and instructions for performing the techniques described above, for example. This memory may also be used for storing variables or other intermediate information during execution of instructions to be executed by processor  601 . Possible implementations of this memory may be, but are not limited to, random access memory (RAM), read only memory (ROM), or both. A storage device  603  is also provided for storing information and instructions. Common forms of storage devices include, for example, a hard drive, a magnetic disk, an optical disk, a CD-ROM, a DVD, a flash memory, a USB memory card, or any other medium from which a computer can read. Storage device  603  may include source code, binary code, or software files for performing the techniques above, for example. Storage device and memory are both examples of computer readable mediums. 
         [0100]    Computer system  610  may be coupled via bus  605  to a display  612 , such as a cathode ray tube (CRT) or liquid crystal display (LCD), for displaying information to a computer user. An input device  611  such as a keyboard and/or mouse is coupled to bus  605  for communicating information and command selections from the user to processor  601 . The combination of these components allows the user to communicate with the system. In some systems, bus  605  may be divided into multiple specialized buses. 
         [0101]    Computer system  610  also includes a network interface  604  coupled with bus  605 . Network interface  604  may provide two-way data communication between computer system  610  and the local network  620 . The network interface  604  may be a digital subscriber line (DSL) or a modem to provide data communication connection over a telephone line, for example. Another example of the network interface is a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links are another example. In any such implementation, network interface  604  sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information. 
         [0102]    Computer system  610  can send and receive information, including messages or other interface actions, through the network interface  604  across a local network  620 , an Intranet, or the Internet  630 . For a local network, computer system  610  may communicate with a plurality of other computer machines, such as server  615 . Accordingly, computer system  610  and server computer systems represented by server  615  may form a cloud computing network, which may be programmed with processes described herein. In the Internet example, software components or services may reside on multiple different computer systems  610  or servers  631 - 635  across the network. The processes described above may be implemented on one or more servers, for example. A server  631  may transmit actions or messages from one component, through Internet  630 , local network  620 , and network interface  604  to a component on computer system  610 . The software components and processes described above may be implemented on any computer system and send and/or receive information across a network, for example. 
         [0103]    The above description illustrates various embodiments of the present invention along with examples of how certain aspects may be implemented. The above examples and embodiments should not be deemed to be the only embodiments, and are presented to illustrate the flexibility and advantages of the present invention as defined by the following claims. Based on the above disclosure and the following claims, other arrangements, embodiments, and implementations may be employed.