Computer apparatus and method for caching results of a database query

• US 7,089,228 B2
• Filed: 04/18/2002
• Issued: 08/08/2006
• Est. Priority Date: 04/18/2002
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Barsness, Eric Lawrence, Dettinger, Richard Dean, Santosuosso, John Matthew, Arnold, Jeremy Alan
• Primary Examiner(s): Corrielus, Jean M.
• Application Number:
  US10/125,190
• Publication Number:
  US 20030200194A1
• Time in Patent Office: 1,573 Days
• Field of Search: 707/2, 707/3
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A query processor caches data retrieved from executing prepared statements, and uses the cached data for subsequent accesses to the data, if certain conditions for using the cached data are met. The preferred embodiments also include a data staleness handler that takes care of issues that arise from data that may have changed in the database but is not reflected in the cache. One way to handle data staleness in the cache is to specifically enable or disable caching in a query. If caching is disabled, the query processor will access the data in the database. Another way to handle data staleness in the cache is to provide a timer that causes the cache to be invalidated when the timer times out. Yet another way to handle data staleness in the cache is to provide specified conditions that must be met for caching to occur, such as time or date limitations. Still another way to handle data staleness in the cache is to provide an update trigger for the data in the database that corresponds to the cached data. When the data in the database is updated, the update trigger fires, causing the cache to be invalidated. Note that invalidating the cache could also be followed by automatically updating the cache. By caching the results of processing a prepared statement, other queries that use the same prepared statement may be able to access data in the cache instead of going to the database.

Claims

1. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a database residing in the memory;

a query processor residing in the memory and executed by the at least one processor, the query processor caching data retrieved from the database as a result of a first query, and returning cached data when processing a second query to the database instead of accessing data in the database; and

a data staleness handler that determines from a specified parameter in the second query whether use of cached data is enabled when processing the second query and does not return the cached data when processing the second query if a specified range of values for using the cached data is not satisfied or if the specified parameter specifies that use of cached data is disabled when processing the second query.

2. The apparatus of claim 1 wherein the first query comprises a prepared statement having a first specified value, and wherein the second query comprises the prepared statement having a second specified value, wherein the second query is capable of being satisfied by data retrieved from the database during the processing of the first query.

3. The apparatus of claim 1 wherein the specified range of values comprises a range of times during which use of the cached data is not allowed.

4. The apparatus of claim 1 wherein the specified range of values comprises a range of dates during which use of the cached data is not allowed.

5. The apparatus of claim 1 wherein the data staleness handler monitors selected data in the database that corresponds to at least a portion of the cached data, and that invalidates the cached data when the selected data in the database changes.

6. The apparatus of claim 5 wherein the data staleness handler updates the cached data by retrieving the changed data from the database when the cached data is invalidated.

7. The apparatus of claim 1 wherein the data staleness handler uses a timer to invalidate the cached data when the timer times out.

8. The apparatus of claim 7 wherein the data staleness handler updates the cached data by retrieving the changed data from the database when the cached data is invalidated.

9. The apparatus of claim 7 wherein the query processor may bypass the cached data and return data from the database under specified conditions.

10. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

a database residing in the memory;

a cache residing in the memory;

a query processor residing in the memory and executed by the at least one processor, the query processor storing data retrieved from the database into the cache as a result of a first query, and returning data from the cache when processing a second query to the database instead of accessing the data in the database if specified conditions for returning the cached data are satisfied;

a data staleness handler residing in the memory and executed by the at least one processor, wherein the data staleness handler indicates to the query processor when the cached data cannot be returned, wherein the data staleness handler indicates to the query processor that the cached data cannot be returned when processing the second query if use of cached data is disabled in the second query, wherein the data staleness handler indicates to the query processor that the cached data cannot be returned during a specified range of times, wherein the data staleness handler indicates to the query processor that the cached data cannot be returned during a specified range of dates, wherein the data staleness handler monitors selected data in the database that corresponds to at least a portion of the cached data and invalidates the cached data when the selected data in the database changes, wherein the data staleness handler uses a timer to invalidate the cached data when the timer times out, wherein the data staleness handler updates the cached data by retrieving the changed data from the database into the cache when the cached data is invalidated.

11. A computer-implemented method for processing a second query to a database, the method comprising the steps of

(A) processing a first query to the database by performing the steps of

(A1) retrieving data from the database that satisfies the first query;

(A2) storing the retrieved data in a cache; and

(A3) returning the retrieved data;

(B) processing the second query by performing the steps of

(B1) determining from a specified parameter in the second query whether use of cached data is allowed in processing the second query;

(B2) determining whether the second query is capable of being satisfied by data stored in the cache;

(B3) determining whether use of cached data is allowed depending on whether a specified range of values for using the cache is satisfied;

(B4) if the query is capable of being satisfied by data stored in the cache and the use of cached data is allowed, retrieving data from the cache that satisfies the second query, and returning the data retrieved from the cache;

(B5) if the query is not capable of being satisfied by data stored in the cache, retrieving data from the database that satisfies the second query, and returning the data that satisfies the second query;

(B6) if the use of cached data is not allowed as determined instep (B1), retrieving data from the database that satisfies the second query, and returning the retrieved data that satisfies the second query.

12. The method of claim 11 wherein the specified range of values comprises a range of times during which use of the cached data is not allowed.

13. The method of claim 11 wherein the specified range of values comprises a range of dates during which use of the cached data is not allowed.

14. The method of claim 11 further comprising the steps of:

monitoring data in the database that corresponds to cached data; and

invalidating the cached data when the corresponding data in the database changes.

15. The method of claim 14 further comprising the step of updating the cached data by retrieving the changed data from the database into the cache when the cached data is invalidated.

16. The method of claim 11 further comprising the step of invalidating the cached data when a timer times out.

17. The method of claim 16 further comprising the step of updating the cached data by retrieving the changed data from the database into the cache when the cached data is invalidated.

18. The method of claim 16 further comprising the step of bypassing the cached data and retrieving the data from the database under specified conditions.

19. A computer-implemented method for processing a second query to a database, the method comprising the steps of:

(A) processing a first query to the database by performing the steps of;

(A1) retrieving data from the database that satisfies the first query;

(A2) storing the retrieved data in a cache; and

(A3) returning the retrieved data;

(B) processing the second query by performing the steps of;

(B1) determining whether the second query is capable of being satisfied by data stored in the cache;

(B2) determining whether caching is disabled in the second query;

(B3) determining whether the second query specifies a range of times during which use of the cached data is not allowed;

(B4) determining whether the second query specifies a range of dates during which use of the cached data is not allowed;

(B5) if the query is capable of being satisfied by data stored in the cache, if step (B2) indicates that caching is not disabled, and if steps (B3) and (B4) indicate that use of the cached data is allowed, retrieving the data from the cache that satisfies the second query and returning the data that satisfies the second query;

(B6) if the query is not capable of being satisfied by data stored in the cache, retrieving data from the database that satisfies the second query, and returning the data that satisfies the second query;

(B7) if step (B2) indicates that caching is disabled in the second query, retrieving data from the database that satisfies the second query, and returning the retrieved data that satisfies the second query; and

(B8) if either of steps (B3) and (B4) indicate that use of the cached data is not allowed, retrieving data from the database and returning the data retrieved from the database.

20. The method of claim 19 further comprising the steps of:

monitoring data in the database that corresponds to cached data; and

invalidating the cached data when the corresponding data in the database changes.

21. The method of claim 20 further comprising the step of updating the cached data by retrieving the changed data from the database into the cache when the cached data is invalidated.

22. The method of claim 19 further comprising the step of invalidating the cached data when a timer times out.

23. The method of claim 22 further comprising the step of updating the cached data by retrieving the changed data from the database into the cache when the cached data is invalidated.

24. A program product comprising:

(A) a query processor that caches data retrieved from a database as a result of a first query, and returns cached data when processing a second query to the database instead of accessing data in the database wherein the query processor includes a data staleness handler that determines from a specified parameter in the second query whether use of cached data is enabled when processing the second query and does not return the cached data when processing the second query if a specified range of values for using the cached data is not satisfied or if the specified parameter specifies that use of cached data is disabled when processing the second query; and

(B) recordable computer-readable signal bearing media bearing the query processor.

25. The program product of claim 24 wherein the first query comprises a prepared statement having a first specified value, and wherein the second query comprises the prepared statement having a second specified value, wherein the second query may be satisfied by data retrieved from the database during the processing of the first query.

26. The program product of claim 24 wherein the specified range of values comprises a range of times during which use of the cached data is not allowed.

27. The program product of claim 24 wherein the specified range of values comprises a range of dates during which use of the cached data is not allowed.

28. The program product of claim 24 wherein the data staleness handler monitors selected data in the database that corresponds to at least a portion of the cached data, and that invalidates the cached data when the selected data in the database changes.

29. The program product of claim 28 wherein the data staleness handler updates the cached data by retrieving the changed data from the database when the cached data is invalidated.

30. The program product of claim 24 wherein the data staleness handler uses a timer to invalidate the cached data when the timer times out.

31. The program product of claim 30 wherein the data staleness handler updates the cached data by retrieving the changed data from the database when the cached data is invalidated.

32. The program product of claim 24 wherein the query processor may bypass the cached data and return data from the database under specified conditions.

33. A program product comprising:

(A) a query that stores data retrieved from a database into a cache as a result of a first query, and returns data from the cache when processing a second query to the database instead of accessing the data in the database if specified conditions for returning the cached data are satisfied;

(B) a data staleness handler that indicates to the query processor when the cached data cannot be returned, wherein the data staleness handler indicates to the query processor that the cached data cannot be returned when processing the second query if use of cached data is disabled in the second query, wherein the data staleness handler indicates to the query processor that the cached data cannot be returned during a specified range of times, wherein the data staleness handler indicates to the query processor that the cached data cannot be returned during a specified range of dates, wherein the data staleness handler monitors selected data in the database that corresponds to at least a portion of the cached data and invalidates the cached data when the selected data in the database changes, wherein the data staleness handler uses a timer to invalidate the cached data when the timer times out, wherein the data staleness handler updates the cached data by retrieving the changed data from the database into the cache when the cached data is invalidated; and

(C) recordable computer-readable signal bearing media bearing the query processor and the data staleness handler.