System and method for querying a resource cache

Abstract

The invention relates to systems and methods for caching resources for queries. Operations include determining whether to cache a resource accessed by a query based on an execution time of the query. The system identifies a set of executions of the same query. The system determines a cumulative execution time for the set of executions of the same query. If the cumulative execution time exceeds a threshold, the system caches a resource used to execute the query.

Description

[0001] This application is a divisional of PCT application entering Chinese national phase, with international application date of 28 December 2017, national application number 201780090982.X, and titled "System and method for querying resource cache." TECHNICAL FIELD

[0002] The present disclosure relates to resource caching. In particular, the present disclosure relates to selectively caching resources accessed by queries.

[0003] CLAIM OF BENEFIT

[0004] This application claims the benefit of and priority to U.S. Non-Provisional Application No. 15 / 600,518, filed May 19, 2017, which is incorporated by reference herein. BACKGROUND

[0005] A cache can refer to hardware and / or software used to store data. Retrieving data from a cache is typically faster than retrieving data from a hard drive or any storage system that is remote from the execution environment. Most commonly, a cache stores recently used data. A cache can store a copy of data that is stored elsewhere, and / or store the results of a computation. Web-based caching is also common, where a web cache between a server and a client stores data. A client can access data from a web cache faster than from data in a server.

[0006] A query fetches specified data from a database. Typically, the data is stored in a relational database. A relational database stores data in one or more tables. These tables are composed of data rows and organized into fields or columns. For example, "FirstName" and "LastName" are fields of a data table, and the number of rows therein is the number of names stored to the table.

[0007] Structured Query Language (SQL) is a language used to manage data in a relational database. SQL queries retrieve data based on specified criteria. Most SQL queries use the statement SELECT to retrieve data. SQL queries can then specify criteria such as FROM - which tables contain the data; JOIN - specify rules for joining tables; WHERE - limit the rows returned by the query; GROUP BY - aggregate duplicate rows; and ORDER BY - specify the order of sorting of the data. For example, the SQL query "SELECT breed, age, name FROM Dogs WHERE age < 3 ORDER BY breed" would retrieve the breed, age, and name of each dog from the "Dogs" table that is under 3 years old, sorted alphabetically by breed. The output would look like: "Bulldog 1 Max | Cocker Spaniel 2 Joey | Golden Retriever 1.5 Belinda".

[0008] Increasingly, databases are stored using multi-tenant cloud architectures. In a multi-tenant cloud architecture, data from different tenants is stored using shared resources. The shared resources can be some combination of all or part of servers, databases, and / or tables. Multi-tenancy reduces the amount of resources needed to store data, thereby saving costs.

[0009] The methods described in this section are methods that can be pursued, but not necessarily ones that have been previously conceived or pursued. Therefore, unless otherwise indicated herein, it should not be assumed that any of the methods mentioned in this section qualify as prior art merely by virtue of their inclusion in this section. BRIEF DESCRIPTION OF DRAWINGS

[0010] Embodiments are illustrated by way of example and not by way of limitation in the figures of the accompanying drawings. It should be noted that references to "an" or "one" embodiment in this disclosure are not necessarily to the same embodiment, and they mean at least one. In the drawings:

[0011] Figure 1 illustrates a resource caching system in accordance with one or more embodiments;

[0012] Figure 2 illustrates an example set of operations for selective caching by query in accordance with one or more embodiments;

[0013] Figure 3 illustrates an example set of operations for selective caching by resource in accordance with one or more embodiments;

[0014] Figure 4FIGURE illustrates an example set of operations for selective caching by JOIN, in accordance with one or more embodiments;

[0015] Figure 5 FIGURE illustrates a block diagram of a system, in accordance with one or more embodiments. DETAILED DESCRIPTION

[0016] In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding. One or more embodiments can be practiced without these specific details. Features described in one embodiment can be combined with features described in different embodiments. In some examples, well-known structures and devices are described with reference to a block diagram form in order to avoid unnecessarily obscuring the present description.

[0017] 1. OVERALL SUMMARY

[0018] 2. RESOURCE CACHING SYSTEM

[0019] 3. QUERY-BASED RESOURCE CACHING

[0020] 4. RESOURCE-USE-BASED RESOURCE CACHING

[0021] 5. OPERATION-BASED RESOURCE CACHING

[0022] 6. EXAMPLE EMBODIMENT - AGGREGATE QUERIES

[0023] 7. OTHER MATTERS; EXTENSIONS

[0024] 8. HARDWARE OVERVIEW

[0025] 1. OVERALL SUMMARY

[0026] One or more embodiments include selectively caching resources accessed by queries. The cached resources can be continuously or periodically updated in response to original copies of the resources being updated. Maintaining up-to-date resources in the cache allows queries to be performed by accessing the resources in the cache rather than accessing the resources from disk or other primary storage. The resources for caching can be selected based at least on execution times of corresponding queries. In an example, if an execution of a query exceeds a threshold execution time, resources accessed by the query are cached for future execution of the same query.

[0027] One or more embodiments include caching resources accessed by queries based on cumulative execution times of executions of the queries. A cache engine can determine a cumulative execution time for an execution of a query during an initial time period. The cache engine can also determine whether to cache a resource to be accessed during the execution of the query based on the cumulative execution time of the execution of the query. After the initial time period, the resource can be cached for another time period.

[0028] The cache engine can use any method to determine which resources to cache based on the cumulative execution time of the corresponding query. In an example, if the cumulative execution time of the query during the initial time period exceeds a threshold, the resources accessed by the query are cached for a subsequent time period. In another example, the queries are ranked based on the cumulative execution time. The resources for the n queries with the longest cumulative execution time are cached.

[0029] One or more embodiments include caching resources accessed by a query based on at least the execution time of a subset of the query's executions. The execution time of each execution of the query during an initial time period is compared to a threshold. If the execution time of any particular execution exceeds the threshold, the particular execution is determined to be a computationally expensive execution. If the computationally expensive execution of the query exceeds the threshold, the resources for the query are cached for a subsequent time period.

[0030] One or more embodiments described in this specification and / or set forth in the claims can not be included in this general overview section.

[0031] 2. Resource caching system

[0032] Figure 1 A resource caching system 100 is illustrated in accordance with one or more embodiments. The resource caching system 100 is a system for selecting and caching resources accessed for executing a query (which can be referred to herein as resources accessed by a query). The resource caching system 100 includes a query interface 102, a cache engine 104, a cache 124, a query execution engine 122, and a data repository 110. In one or more embodiments, the resource caching system 100 can include more or fewer components than those shown. Figure 1 The components shown can be more or fewer than those shown. Figure 1 The components shown can be local to or remote from each other. Figure 1 The components shown can be implemented in software and / or hardware. Each component can be distributed across multiple applications and / or machines. Multiple components can be combined into a single application and / or machine. Operations described with respect to one component can instead be performed by another component.

[0033] In one or more embodiments, the query interface 102 is an interface that includes functionality to accept input defining a query. The query interface 102 can be a user interface (UI), such as a graphical user interface (GUI). The query interface can present user-modifiable fields that accept a query profile describing a query. The query interface 102 can include functionality to accept and parse a file defining one or more queries. The query interface can display query output data after execution of a query.

[0034] In embodiments, the query execution engine 122 includes hardware and / or software components for executing queries. The query execution engine 122 can parse a query profile received from the query interface. The query execution engine 122 can map the parsed query profile to a SQL query. The query execution engine 122 can send the SQL query to the appropriate database(s) to retrieve query results. The query execution engine 122 can sum data, average data, and combine tables in whole or in part.

[0035] In embodiments, the cache 124 corresponds to hardware and / or software components that store data. Data stored in the cache 124 can generally be accessed more quickly than data stored on disk, main memory, or stored remotely from the execution environment. In examples, the cache 124 stores resources that have previously been retrieved from disk and / or main memory (referred to herein as “cached resources 126”) for executing queries. Storing resources in the cache can allow additional executions of the same query without accessing the resources from disk. In particular, resources required by a query are accessed from the cache rather than from disk. The cache can be updated continuously or periodically in response to original copies of data stored in disk being updated. Each data set or resource in the cache 124 can be maintained with a flag indicating whether the data is current or outdated. Cached resources can be, for example, data tables, data fields, and / or results of computations. As an example, the cached resources 126 can be a new table created via a JOIN operation on two existing tables.

[0036] In embodiments, the data repository 110 is any type of storage unit and / or device (e.g., a file system, a database, a collection of tables, or any other storage mechanism) for storing data. Additionally, the data repository 110 can include multiple different storage units and / or devices. The multiple different storage units and / or devices can or can not be of the same type or located at the same physical site. Furthermore, the data repository 110 can be implemented or executed on the same computing system as the cache engine 104, the cache 124, the query interface 102, and the query execution engine 122 or on a different computing system from the cache engine 104, the cache 124, the query interface 102, and the query execution engine 122. The data repository 110 can be communicatively coupled to the cache engine 104, the cache 124, the query interface 102, and the query execution engine 122 via a direct connection or via a network.

[0037] In an embodiment, the data repository 110 stores query profiles 112. The query profiles 112 include information about queries. The query profiles include, but are not limited to, query attributes 114, query execution times 116, and query resources 118. Query profiles can be selected based on query performance. Query profiles 112 can be stored for selected queries that have individual execution times above a certain individual threshold.

[0038] In an embodiment, the query attributes 114 can include one or more operations performed or to be performed in a query. For example, in the query "SELECT Customers.CustomerName, Customers.CustomerID FROM Customers," the operation SELECT is a query attribute 114. Other examples of query attributes 114 include the order in which a series of operations are performed and the time of day a query is executed.

[0039] In an embodiment, within a query profile for a query, the query resources 118 identify one or more of the resources 120 used to execute the corresponding query. The query resources 118 can include any data set specified in the query. The query resources can correspond to fields. For example, in the query "SELECT Customers.CustomerName, Customers.CustomerID FROM Customers," the query resources 114 include the fields CustomerName and CustomerID. The fields CustomerName and CustomerID are examples of query resources 118. The query resources 118 can include tables in a database used to retrieve the data requested in the query. For example, in the query above, the table Customers is a query resource 118. As described above, the query resources 118 can be cached in the cache 124 and referred to as cached resources 126.

[0040] In this embodiment, query execution time 116 is the time corresponding to a specific execution of the query. Query execution time 116 can be the time period between sending a request to execute a specific query and receiving the result from the execution of that specific query. Examples of query execution times include 1 millisecond, 10 seconds, 16 minutes, and 6 hours. The execution time of the same query can vary for different executions performed at different times. For example, input / output time delays caused by other concurrent access operations may cause the execution time of one execution of a query to be significantly longer than the execution time of a previous execution of the same query during which no other concurrent access operations occurred. Queries can be executed on a multi-tenant cloud architecture supporting multiple users. The system may be overloaded during peak periods when system resources receive requests from multiple users. The time taken to execute a query during peak periods can be longer than during off-peak periods when the system is not overloaded. Query execution time can also depend on factors such as the operations in the query and the number of data tables used to retrieve data for the query.

[0041] In one or more embodiments, cache engine 104 includes hardware and / or software components for caching resources. The cache engine includes functionality to store copies of data and / or computation results to cache 124. Cache engine 104 may selectively cache resources based on the execution time of a corresponding query. Cache engine 104 may cache resources according to standard caching techniques, such as caching recently used data.

[0042] In this embodiment, the query analyzer 106 includes hardware and / or software components for analyzing queries. The query analyzer 106 can analyze query execution time, query attributes, and / or query resources to identify information about a particular query.

[0043] Query analyzer 106 may include the ability to parse queries and isolate data fields included in the query, SQL operations included in the query, and / or data tables in the storage system used to retrieve the requested data. Query analyzer 106 may include the ability to analyze query sets to determine whether one or more query executions constitute the same query. For example, at time 1, the system receives query Q from user 1. a = (f1, f2, f3), where f i These are the data fields to be retrieved in the query. At time 2, the system receives query Q from user 2. b = (f2, f1, f3). Although the elements are in different orders at different times, Q a and Q b The retrieved data is identical. By analyzing query attribute 114 and query resource 118, the resource caching system 100 can identify multiple executions of the same query.

[0044] The query analyzer 106 can include functionality to compute the execution time of a query. The query analyzer can compute the execution time of a single execution of a query. The query analyzer can compute the cumulative execution time of multiple executions of the same query during a particular time period. The query analyzer 106 can compute the cumulative execution time of multiple executions of the same query by aggregating the execution time of each individual execution of the query.

[0045] In embodiments, the resource analyzer 108 includes hardware and / or software components to analyze resources. The resource analyzer 108 can analyze query execution times, query attributes, and / or query resources to identify information about particular resources.

[0046] The resource analyzer 108 can include functionality to parse a query and isolate data fields included in the query, SQL operations included in the query, and / or data tables in a storage system used to retrieve requested data. The query analyzer 106 can include functionality to analyze a query to determine whether one or more query executions use the same resource. For example, at time 1, the system receives a query Ql, "SELECT Dog.Breed, Dog.Age FROM Dogs," from user 1. At time 2, the system receives a query Q2, "SELECT Dog.Name, Dog.Breed, DogAquisitionDate FROM Dogs," from user 2. The resource analyzer can determine that both Ql and Q2 query the table Dogs.

[0047] The resource analyzer 108 can include functionality to compute the amount of time a particular resource is accessed during a period of time. The resource analyzer 108 can determine the execution time of each query that uses a particular resource during a period of time. The resource analyzer 108 can compute the cumulative execution time of multiple queries that use a particular resource by aggregating the execution time of each individual query.

[0048] 3. Query-based resource caching

[0049] Figure 2 FIGURE 1 illustrates an example set of operations for selectively caching one or more resources based on the same query, in accordance with one or more embodiments. Figure 2 The illustrated operations can be modified, rearranged, or fully omitted. Accordingly, Figure 2 The particular sequence of operations illustrated should not be construed as limiting the scope of one or more embodiments.

[0050] In embodiments, the query analyzer identifies queries that have an execution time above an individual threshold (operation 202). The query analyzer can establish an individual threshold K1 for comparison with the execution time of individual queries. The value of K1 can be established based on, for example, the complexity of the query, user preferences, and available system resources. The query analyzer compares the execution time of a query to K1 to determine whether the execution time of the query exceeds K1.

[0051] For queries that have an execution time that exceeds the individual threshold, the resource caching system can store a query log. For a subset of queries that have an execution time above the individual threshold, such as queries that include a SELECT query operation, the resource caching system can store a query log.

[0052] Operation 202 can be used to identify candidate queries to be analyzed in operation 204. Alternatively, operation 202 can be skipped, and all queries can be analyzed in operation 204.

[0053] In embodiments, the resource caching system identifies one or more executions of the same query during an initial time period (operation 204). The query analyzer can compare query attributes of multiple queries to determine whether the queries are the same. For example, the query execution engine executed the following queries Q1-Q6 using a SELECT query operation during a one-month time period:

[0054] Q1 = (f1, f2, f3, f4, f5)

[0055] Q2 = (f1, f2, f3, f4, f5)

[0056] Q3 = (f3, f9, f2, f5, f4)

[0057] Q4 = (f1, f4, f3, f5, f2)

[0058] Q5 = (f5, f6, f7, f8, f9)

[0059] Q6 = (f6, f2, f3, f7, f5)

[0060] Fields f1-f9 are data fields that are selected in the queries. The query analyzer compares the data field values to identify identical queries. Identical queries select the same data fields, although not necessarily in the same order. The resource caching system identifies Q1 = Q2 = Q4 as three executions of the same query that occurred during the month.

[0061] In embodiments, the execution of a particular stored query is determined via a log. Specifically, the cache system maintains a log to track all executions of a stored query. Each query is associated with a profile. The profile includes characteristics of each execution of the query. The profile can store the run time of each execution of the query.

[0062] In embodiments, the cache engine aggregates the execution times of the multiple executions of the query to compute the cumulative execution time of the query during the initial time period (operation 206). For example, the query has been executed 6 times in a day. The system has stored the 6 corresponding execution times: T1=2 minutes, T2=1 hour, T3=20 minutes, T4=5 minutes, T5=1 hour 22 minutes, and T6=30 seconds. The system computes the cumulative execution time of the query during a time period:

[0063] T tot = T1+ T2+ T3+ T4+ T5+ T6

[0064] = 2 minutes + 1 hour + 20 minutes + 5 minutes + 1 hour 22 minutes + 30 seconds

[0065] = 2 hours 49 minutes 30 seconds

[0066] The cumulative execution time of the query during the time period of a day is T tot = 2 hours 49 minutes 30 seconds.

[0067] The query analyzer can use the executions of the same query that occurred during a time period to compute the cumulative execution time, as shown above. Alternatively, the query analyzer can use a subset of the executions of the same query that occurred during a time period to compute the cumulative execution time. For example, the query analyzer filters the query executions to include query executions with execution times exceeding a threshold query execution time K1. When K1=15 minutes, the system will store the execution instances - T2, T3, and T5 - with query times exceeding 15 minutes. The system will then use the filtered query to compute the cumulative query time:

[0068] T K1 = T2+ T3+ T5

[0069] = 1 hour + 20 minutes + 1 hour 22 minutes

[0070] = 2 hours 44 minutes

[0071] The cumulative execution time of the query during the time period of a day of interest is T K1 = 2 hours 44 minutes.

[0072] In embodiments, the cache engine determines whether the cumulative execution time exceeds a cumulative threshold (operation 208). For example, the cumulative threshold is K2=2 hours. For the above T K1, the cumulative execution time is 2 hours and 44 minutes. In this case, T K1 > K2, and the cumulative execution time exceeds the cumulative threshold.

[0073] If the cumulative execution time exceeds the threshold, the cache engine caches the resource(s) required by the query for another time period after the initial time period (operation 210). For example, the cache engine can cache each table containing the selected fields in the query. The cache engine can cache the output of the query. For example, the query selects four fields from a table. The cache engine can cache the data in the four selected fields. The cache engine can retain the resource(s) for a particular amount of time or overwrite the resource(s) in response to detecting the occurrence of a particular event.

[0074] If the cumulative execution time does not exceed the threshold, the cache engine can refrain from caching the resource(s) required by the query (operation 212). By refraining from caching resources required by a query that runs quickly, the resource cache system conserves memory in the cache and avoids unnecessary operations.

[0075] In an embodiment, operation 212 can be omitted from the sequence of operations. For example, although the resource cache system described above does not select a particular resource to cache, the system can still cache the resource based on another caching method. The system can cache the resource immediately after use in accordance with a standard caching technique that includes caching of resources used within the last 30 seconds.

[0076] As an example, the resource cache system identifies queries with a SELECT operation that are executed during a one-year period and have an execution time higher than 1 minute. Out of 10,000 queries executed during the year, there are 10 that include a SELECT operation and take more than 1 minute to execute. The query logs for the 10 queries are captured in a table (Table 1).

[0077] For each query in Table 1, the resource cache system captures the data fields selected using the SELECT query operation. These fields are f i where i = 1,..., n and n is the total number of data fields that appear at least once in the queries of Table 1. Here, Table 1 stores the query logs for the following 10 queries:

[0078] Q1 = (f1, f2, f3, f4, f5)

[0079] Q2 = (f 11, f 12 , f 15 )

[0080] Q3 = (f3, f9, f 12 , f5, f 10 )

[0081] Q4 = (f1, f4, f3, f5, f2)

[0082] Q5 = (f5, f6, f7, f8, f9)

[0083] Q6 = (f 11 , f 12 , f 15 )

[0084] Q7 = (f1, f2, f3, f4, f5)

[0085] Q8 = (f 13 , f9, f 20 , f5, f4, f 18 , f 11 , f8, f7)

[0086] Q9 = (f1, f4, f3, f5, f2)

[0087] Q 10 = (f 15 , f 16 , f 17 , f 18 , f 19 , f1, f2, f3, f4)

[0088] The resource cache system identifies a unique combination Q k = (f k1 ,..., f kl ) corresponding to at least one query in Table 1. The resource cache system identifies a set S k = (f k1 ,..., f k1 ) of queries that contain the same combination Q k of data fields. Table 1 contains 6 unique combinations:

[0089] S1 = {Q1, Q4, Q7, Q9}

[0090] S2 = {Q2, Q6}

[0091] S3 = {Q3}

[0092] S4 = {Q5}

[0093] S5 = {Q8}

[0094] S6 = {Q 10}

[0095] Set 1 includes Q1, Q4, Q7, and Q9 because these queries select the same five data fields, although not necessarily in the same order. Set 2 includes queries Q2 and Q6 because these queries select the same three data fields. Sets S3-S6 each contain a unique query - there are no repeated Q3, Q5, Q8, or Q 10 .

[0096] For each set S k , the resource cache system calculates the cumulative execution time for the queries from set S k . For S1, the execution times are:

[0097] Q1: t1 = 2 minutes

[0098] Q4: t4 = 1 hour

[0099] Q7: t7 = 30 minutes

[0100] Q9: t9 = 3 minutes

[0101] The resource cache system calculates the cumulative execution time for set S1:

[0102] T1 = t1 + t4 + t7 + t9

[0103] = 2 minutes + 1 hour + 30 minutes + 3 minutes

[0104] = 1 hour 35 minutes

[0105] Similarly, the resource cache system calculates the cumulative execution time for sets S2-S6.

[0106] Next, the resource cache system determines whether the cumulative execution time for a particular set of queries exceeds the cumulative threshold K2 = 1 hour. For S1, the cumulative execution time is 1 hour 35 minutes, which exceeds the cumulative threshold of 1 hour.

[0107] Upon determining that the cumulative execution time exceeds the threshold for S1, the cache engine caches the resources required by the queries. The cache engine creates a cache table Al in the cache, thereby caching the resources required to execute the SQL command "SELECT f1, f2, f3, f4, f5. FROM Z1". For all unique combinations Q k and their corresponding sets S k , the resource cache system repeats the process of selectively caching resources based on the total execution time in the set.

[0108] 4. Resource caching based on resource usage

[0109] Figure 3 FIGURE 1 illustrates an example set of operations for selectively caching resources based on their usage, according to one or more embodiments. Figure 2 One or more of the illustrated operations can be modified, rearranged, or completely omitted. As such, Figure 2 The particular order of the operations illustrated should not be construed as limiting the scope of one or more embodiments.

[0110] In an embodiment, the resource analyzer identifies the executions of queries for the same resource during an initial time period (operation 302). The resource analyzer can monitor queries executed by the query execution engine over the initial time period. The resource analyzer can use a pull method to pull data from the query execution engine, thereby identifying the executions of queries. The query execution engine can use a push method to push data from the query execution engine to the resource analyzer. The resource analyzer can map each resource accessed during the initial time period to one or more queries executed during the initial time period.

[0111] In an embodiment, the resource analyzer aggregates the execution times of queries that use each particular resource during the time period to compute a cumulative execution time for each particular resource during the time period (operation 304). For example, during a particular day, 100 queries are executed. Five of these queries request information from a particular table. The system has stored five execution times corresponding to the five queries: tl = 2 minutes, t2 = 1 hour, t3 = 20 minutes, t4 = 5 minutes, and t5 = 1 hour 22 minutes. The system computes the cumulative execution time of queries that use the resource during the initial time period:

[0112] T tot = tl + t2 + t3 + t4 + t5

[0113] = 2 minutes + 1 hour + 20 minutes + 5 minutes + 1 hour 22 minutes

[0114] = 2 hours 49 minutes

[0115] The cumulative execution time of queries that use the resource during the time period of a day is T tot = 2 hours 49 minutes.

[0116] As described above, the resource analyzer can compute the cumulative execution time of all executions of queries for the same resource during the initial time period. Alternatively, the resource analyzer can use a subset of the executions of queries for the same resource during a time period to compute the cumulative execution time. For example, the resource analyzer filters the query executions to include executions with run times exceeding an individual threshold Kl.

[0117] In this embodiment, the resource analyzer determines whether the cumulative execution time exceeds a cumulative threshold (operation 306). If the cumulative execution time exceeds the threshold, the caching engine caches the resource (operation 308). If the cumulative execution time does not exceed the threshold, the caching engine can avoid caching the resource (operation 310). Operations 306, 308, and 310 are similar to operations 208, 210, and 212 described above, respectively.

[0118] As an example, the resource caching system monitors queries executed by the query execution engine over a 24-hour period. The resource caching system creates a table (Table 2) that stores records of queries accessing the "Ingredients" table. The resource caching system determines that six queries accessed the "Ingredients" table within the 24-hour period of interest. The resource caching system stores the records of the six queries, along with the corresponding execution time for each of the six queries, in Table 2: Q1, t1 = 10 min; Q 12 , t 12 = 1 minute, Q 30 , t 30 = 4 minutes; Q 16 , t 16 = 8 minutes. Q 27 , t 27 = 80 minutes; Q5, t5 = 5 minutes.

[0119] Next, the resource cache system aggregates the execution times of six queries using the resource "Ingredients" during the 24-hour period in Table 2. By adding the six execution times together, the system calculates the cumulative execution time for "Ingredients" over the following time period: t1 + t 12 +t 30 +t 16 +t 27 +t5 = 10 minutes + 1 minute + 4 minutes + 8 minutes + 80 minutes + 5 minutes

[0120] = 108 minutes.

[0121] The resource caching system compares the cumulative execution time to a cumulative threshold of 60 minutes. Because the cumulative execution time of 108 minutes exceeds the 60-minute threshold, the resource caching system caches the resource. The caching engine caches the Ingredients table in the cache.

[0122] 5. Operation-based resource caching

[0123] Figure 4An example set of operations for selectively caching results of operations is illustrated in accordance with one or more embodiments. In particular, Figure 4 An example is illustrated in which the results of a JOIN operation are cached. However, other embodiments can be equally applicable to caching the results of another operation. Figure 4 One or more of the illustrated operations can be modified, rearranged, or omitted altogether. As such, Figure 4 The particular order of operations illustrated should not be construed as limiting the scope of one or more embodiments.

[0124] In an embodiment, the cache engine identifies execution of queries that require a particular set of resources for JOINs during an initial time period (operation 402). The cache engine can compare data fields used in JOIN operations that have been executed to identify all executions of queries that require JOINs of the same particular set of data.

[0125] In an embodiment, the resource cache system aggregates execution times of the executions identified in operation 402 to compute a cumulative execution time of queries that require JOINs of the same particular set of resources (operation 404). The resource cache system can aggregate execution times t i Alternatively, the resource cache system can aggregate execution times t i of JOINs of the resource set during a period of time that exceeds an individual threshold K1 i .

[0126] In an embodiment, the cache engine determines whether the cumulative execution time exceeds a cumulative threshold (operation 406). The cumulative threshold can be, for example, K2 = 30 minutes. The resource cache system compares the computed cumulative execution time to the cumulative threshold K2.

[0127] If the cumulative execution time exceeds the threshold, the cache engine caches JOINs of the resource set, or caches each resource set (operation 408). The cache engine can create a cache table and cache JOINs of the two tables. For example, the resource cache system can create a cache table and cache the SQL logic "SELECT f1, f2, f3, FROM Z1, INNER JOIN Z2 ON g1 = g2" to cache the results of this SQL query. Alternatively, the resource cache system can cache the resources used in the JOIN operation in the query. For example, the system caches tables Z1 and Z2.

[0128] If the cumulative execution time does not exceed the threshold, the cache engine can refrain from caching the JOIN of the resource set, and refrain from caching each particular resource set (operation 410). By refraining from caching the resources, the resource cache system can conserve memory in the cache and avoid unnecessary operations.

[0129] In embodiments, operation 410 is omitted from the sequence of operations. For example, the resource cache system can still cache a resource even though the system did not select the resource to cache. The system can cache the resource according to a standard caching mechanism, such as caching resources that were used within the last 30 seconds.

[0130] As an example, the resource cache system identifies the execution of queries that require a SELECT query operation that were executed during a one-week period and that had an execution time higher than 5 minutes. Out of 1000 queries executed during this period, 10 required a SELECT query operation and took more than 5 minutes to execute. The query logs of these 10 queries are captured in Table (Table 3).

[0131] For each query in Table 3, the resource cache system captures the data fields that were selected using a SELECT query operation. These fields are f i , i = 1,..., n and n is the total number of data fields that appear at least once in the queries of Table 3. For each query in Table 3, the resource cache system also captures the two data fields that were used in a JOIN operation: g k1 and g k2 . Each query in Table 3 is represented as a record Q k = (f k1 ,..., f kl , g kl , g k2 ). The resource cache system also stores the execution time t k of each query.

[0132] The query execution engine executes the query "SELECT Customers.CustomerName, Orders.OrderID from Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName". The resource cache system represents the above query as the combination (f k1 , f k2 , g k1 , g k2), where f k1 = Customers.CustomerName, f k2 = Orders.OrderlD, g k1 = Customers.CustomerlD and g k2 = Orders.CustomerID.

[0133] The resource cache system identifies that queries Q1, Q 10 , Q 17 , and Q 26 in Table 3 contain the same unique combination {f k1 , f k2 , g k1 , g k2}. The resource cache system identifies from Table 3 the set of queries containing the same unique combination: Set S1 = {Q1, Q 10 , Q 17 , Q 26}. The resource cache system identifies the corresponding execution times for each query in Set S1: t1 = 10 minutes, t 10 = 15 minutes, t 17 = 20 minutes, and t 26 = 10 minutes.

[0134] The resource cache system aggregates the execution times of the queries in Set S1 to compute the cumulative execution time: T1 = t1 + t 10 + t 17 + t 26 = 10 minutes + 15 minutes + 20 minutes + 10 minutes = 55 minutes.

[0135] Next, the resource cache system determines whether the computed cumulative execution time exceeds the cumulative threshold K2 = 30 minutes. For S1, the cumulative execution time is 55 minutes, which exceeds the cumulative threshold K2 = 30 minutes.

[0136] Upon determining that the cumulative execution time exceeds the threshold for S1, the cache engine caches the join of the resource sets. The cache engine creates a cache table C1 in the cache, thereby caching the SQL logic "SELECT Customers.CustomerName, Orders.OrderID from Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName". For future queries containing this logic, the resource cache system can now use the results from cache table C1 to complete the above SQL logic steps.

[0137] 6. Example Embodiment - Aggregated Queries

[0138] In an embodiment, the resource cache system stores query logs for queries whose execution time exceeds an individual threshold K1 = 1 minute. Another criterion for storing query logs is that the query requires an aggregation operation on data received as a result of a GROUP BY operation. Examples of SQL aggregation operations include AVG, MAX, and MIN. The system can store query logs for queries whose execution time exceeds 1 minute for an aggregation operation to table (Table 4).

[0139] For queries in Table 4, the system captures the data fields (f i ) selected using the SELECT query operation as well as the data fields (g j ) used by the GROUP BY query operation. The indices are defined as: i = 1,..., n, where n is the total number of data fields that appear at least once in the query of Table 4, and j = 1,..., m, where m is the total number of data fields that appear at least once in the GROUP BY operation in the query of Table 4. For example, Table 4 includes a record Q1 = (f1, f2, f3, g1, g2) representing query 1. Record Q1 implies that the SQL logic "SELECT f1, f2, f3 GROUP BY g1, g2" was applied in query 1. For each query in Table 4, the system represents record Q k = (f k1 ,... f k1 , g k1 ,... g km ) along with t k , t k is the execution time of query Q k . Another threshold K2 is defined. K2 is a cumulative query threshold of 30 minutes.

[0140] The query execution engine executes the query Ql = (fl, f2, f3, gl = f2, g2 = f3) using the SQL logic "SELECT fl, f2, f3, GROUP BY f2, f3". The resource cache system identifies the query Ql, Q 12 , Q 15 , and Q 23 contain the unique combination (fl, f2, f3, gl = f2, g2 = f3). The resource cache system identifies the query set S1 = {Ql, Q 12 , Q 15 , Q 23}. The execution times of the queries in S1 are: tl = 20 minutes, t 12 = 10 minutes, t 15 = 25 minutes, and t 23 = 15 minutes.

[0141] The resource cache system calculates the cumulative execution time of the queries in S1 : Tl = tl + t 12 + t 15 + t 23 = 20 minutes + 10 minutes + 25 minutes + 15 minutes = 70 minutes. Since the cumulative execution time exceeds 30 minutes, the resource cache system determines that Tl > K2. Accordingly, the resource cache system creates a cache table Dl, thereby capturing the data according to the SQL logic "SELECT fl, f2, f3, GROUP BY f2, f3". The system uses the results from the cache table Dl in future queries to complete the SQL logic step "SELECT fl, f2, f3, GROUP BY f2, f3". For example, fl = "revenue", f2 = gl = "region", and f3 = g2 = "vertical". The cache engine caches the results of the query "SELECT revenue, region, vertical GROUP BY region vertical". The next time the system executes a query that requires the above SQL logic, the query execution engine will use the results from the cache table Dl to execute the required SQL logic and quickly deliver the results.

[0142] 5. Other matters; extensions

[0143] Embodiments are directed to systems with one or more devices that include hardware processors and are configured to perform any of the operations described herein and / or recited in any of the claims below.

[0144] In an embodiment, a non-transitory computer-readable storage medium comprises instructions that, when executed by one or more hardware processors, cause performance of any of the operations described herein and / or recited in any of the claims.

[0145] Any combination of the features and functionality described herein can be used according to one or more embodiments. In the foregoing specification, embodiments have been described with reference to numerous specific details that can vary from implementation to implementation. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the scope of the application, and what is intended by the applicants to be the scope of the application, is the literal and equivalent scope of the claims that issue from this application, in whatever form that scope can be construed, and any subsequent correction of issued claims, including any subsequent reissue, reexamination or re-issuance of the claims hereto.

[0146] 6. Hardware Overview

[0147] According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing devices can be hard-wired to perform the techniques, or can include digital electronic devices such as one or more application-specific integrated circuits (ASICs), field programmable gate arrays (FPGAs), or network processing units (NPUs) that are persistently programmed to perform the techniques, or can include one or more general purpose hardware processors programmed to perform the techniques pursuant to program instructions in firmware, memory, other storage, or a combination. Such special-purpose computing devices can also combine custom hard-wired logic, ASICs, FPGAs, or NPUs with custom programming to accomplish the techniques. The special-purpose computing devices can be desktop computer systems, portable computer systems, handheld devices, networking devices, or any other device implementing the techniques in combination with hard-wired and / or program-logic.

[0148] For example, Figure 5 is a block diagram that illustrates a computer system 500 upon which an embodiment of the application can be implemented. Computer system 500 includes a bus 502 or other communication mechanism for communicating information, and a hardware processor 504 coupled with bus 502 for processing information. Hardware processor 504 can be, for example, a general purpose microprocessor.

[0149] Computer system 500 also includes a read only memory (ROM) 508 or other static storage device coupled to bus 502 for storing static information and instructions for processor 504. A storage device 510, such as a magnetic disk or optical disk, is provided and coupled to bus 502 for storing information and instructions.

[0150] Computer system 500 can be coupled via bus 502 to a display 512, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 514, including alphanumeric and other keys, is coupled to bus 502 for communicating information and command selections to processor 504. Another type of user input device is cursor control 516, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor 504 and for

[0151] Computer system 500 can implement the techniques described herein using customized hard-wired logic, one or more ASICs or FPGAs, firmware and / or program logic which in combination with the computer system causes or programs computer system 500 to be a special-purpose machine. According to one embodiment, the techniques herein are performed by computer system 500 in response to processor 504 executing one or more sequences of instructions contained in main memory 506. Such instructions can be read into main memory 506 from another storage medium, such as storage device 510. Execution of the sequences of instructions contained in main memory 506 causes processor 504 to perform the process steps described herein. In alternative embodiments, hard-wired circuitry can be used in place of or in combination with software instructions.

[0152] The term“storage media” as used herein refers to any non-transitory media that store data and / or instructions that cause a machine to operate in a specific fashion. Such storage media can comprise non-volatile media and / or volatile media. Non-volatile media includes, for example, optical disks or magnetic disks, such as storage device 510. Volatile media includes dynamic memory, such as main memory 506. Common forms of storage media include, for example, a floppy disk, a flexible disk, a hard disk, a solid state drive, magnetic tape, or any other magnetic data storage medium, a CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, NVRAM, any other memory chip or cartridge, content addressable memory (CAM), and ternary content addressable memory (TCAM).

[0153] Storage media differentiate from transmission media in that transmission media participate in the communication of information while storage media, on the other hand, enable the storage and retention of information. The various forms of computer-readable media include, for example, a floppy disk, a flexible disk, hard disk, solid-state drive, magnetic tape, or cassette, a CD-ROM, CDRW, DVD+R, DVD-RW, RAM, ROM, PROM, EPROM, EEPROM, flash memory, magnetic cards, optical cards, or any other suitable medium. Storage media can be volatile, non-volatile, or transitory. By way of example, and not limitation, computer-readable media can comprise computer storage media and communication media. Computer storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules or other data. The system memory 506, the removable storage device 508, and the

[0154] Various forms of media can be involved in carrying one or more sequences of one or more instructions to the processor 504 for execution. For example, the instructions can initially be carried on a magnetic disk or solid state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to the computer system 500 can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal. An infra-red detector can receive the data carried in the infra-red signal and appropriate circuitry can place the data on the bus 502. The bus 502 carries the data to the main memory 506, from which the processor 504 retrieves and executes the instructions. The instructions received by the main memory 506 can optionally be stored on a storage device 510 either before or after execution by the processor 504.

[0155] The computer system 500 also includes a communication interface 518 coupled to the bus 502. The communication interface 518 provides a two-way data communication coupling to a network link 520 that is connected to a local network 522. For example, the communication interface 518 can be an integrated services digital network (ISDN) card, cable modem, satellite modem, or a modem to provide a data communication connection to a corresponding type of telephone line. As another example, the communication interface 518 can be a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links can also be implemented. In any such implementation, the communication interface 518 sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.

[0156] Network link 520 typically provides data communication through one or more networks to other data devices. For example, network link 520 can provide a connection through local network 522 to a host computer 524 or to data equipment operated by an Internet Service Provider (ISP) 526. ISP 526 in turn provides data communication services through the world-wide packet data communication network now commonly referred to as the "Internet" 528. Local network 522 and Internet 528 both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link 520 and through communication interface 518 are examples of transmission media for these digital data streams.

[0157] Computer system 500 can send messages and receive data, including program code, through the network(s), network link 520 and communication interface 518. In the Internet example, a server 530 might transmit a requested code for an application program through Internet 528, ISP 526, local network 522 and communication interface 518.

[0158] The received code can be executed by processor 504 as it is received, and / or stored in storage device 510, or other non-volatile storage for later execution.

[0159] In the foregoing specification, embodiments of the application have been described with reference to numerous specific details that can vary from implementation to implementation. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indicator of the scope of the application, and what is intended by the applicants to be the scope of the application, is the literal and equivalent scope of the claims that issue from this application, in whatever form codified by the U.S. Patent Office (i.e., including any subsequent correctional amendments made by the U.S. Patent Office).

Claims

1. One or more non-transitory computer-readable media comprising instructions that, when executed by one or more hardware processors, cause performance of operations comprising: identifying a first plurality of executions of a first query during a first time period; comparing an execution time of each execution of the first plurality of executions of the first query to a first threshold; identifying a first subset of executions of the first plurality of executions that have corresponding execution times that exceed the first threshold; determining whether a first number of executions in the first subset of executions exceeds a second threshold; in response to determining that the first number of executions in the first subset of executions exceeds the second threshold: caching, for a second time period, a first resource used to execute the first query.

2. The non-transitory computer-readable medium of claim 1, wherein the operations further comprise: identifying a second plurality of executions of a second query during the first time period; comparing an execution time of each execution of the second plurality of executions of the second query to the first threshold; identifying a second subset of executions of the second plurality of executions that have corresponding execution times that exceed the first threshold; determining whether a second number of executions in the second subset of executions exceeds the second threshold; in response to at least determining that the second number of executions in the second subset of executions does not exceed the second threshold: refraining from caching, for the second time period, a second resource used to execute the second query.

3. The non-transitory computer-readable medium of claim 1, wherein the resource is a table.

4. The non-transitory computer-readable medium of claim 1, wherein determining an execution time of a particular query of the first plurality of executions of the first query comprises: determining a time period between sending a request to execute the first query and receiving a result from the execution of the first query.

5. The non-transitory computer-readable medium of claim 1, wherein in response to an execution time of a particular query of the first plurality of executions exceeding the first threshold, the particular query is determined to be computationally expensive.

6. The non-transitory computer readable medium of claim 1, wherein the operations further comprise: storing a log record for the first subset of executions that have corresponding execution times that exceed the first threshold.

7. The non-transitory computer-readable medium of claim 1, wherein the first resource is further stored in response to determining that a cumulative execution time of the first subset of executions exceeds a third threshold.

8. A method comprising the operations of any of claims 1-7.

9. A system comprising at least one device including a hardware processor, the system configured to perform the operations of any of claims 1-7.

Images