Registering for and retrieving database table change information that can be used to invalidate cache entries

• US 7,624,126 B2
• Filed: 06/25/2003
• Issued: 11/24/2009
• Est. Priority Date: 06/25/2003
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Pizzo, Michael Joseph, Smith, Adam Wade, Howard, Robert Michael, Ng, Patrick Yu-Kwan, Guthrie, Scott D.
• Primary Examiner(s): Alam; Hosain T
• Assistant Examiner(s): Saeed; Usmaan
• Application Number:
  US10/603,532
• Publication Number:
  US 20040267824A1
• Time in Patent Office: 2,344 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/9574 of access to content, e.g. by caching
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A server provides Web responses that can include content from data tables in a database. The server maintains a cache (e.g., in system memory) that can store content (including content from data tables) so as to increase the efficiency of subsequently providing the same content to satisfy client Web requests. The server monitors data tables for changes and, when a change in a particular data table occurs, invalidates cached entries that depend on a particular data table. Further, in response to a client Web request for a Web response, the server assigns a database cache dependency to at least a portion of a constructed Web response (e.g., to content retrieved from a data table) based on commands executed during construction of the Web response. The at least a portion of the constructed Web response is subsequently cached in a cache location at the server.

Claims

1. In a computer system that accesses a database having one or more data tables, the computer system configured to provide content from the database to a Web server for inclusion in Web based responses to requests for Web based content, the computer system including a cache configured to cache database content included in Web based responses so as to provide more efficient access to the cached database content when formulating subsequent Web based responses, a method for formulating and caching a Web based response in response to receiving a Web based request for database content, the method comprising the following:

an act of determining whether a cache entry for Web based content requested by a request for Web based content already exists in the cache; and

upon determining that the cache entry does not exist, performing the following;

an act of selecting a data table that is to be monitored for content changes, the selected data table selected from among the one or more data tables of the database;

an act of inserting a record for the selected data table into a separate change notification table, the record including versioning information identifying and corresponding to the selected data table, the versioning information retrievable by the Web server to determine when a corresponding cache entry containing cacheable content from the selected data table is invalid;

an act of assigning a trigger to the selected data table, the trigger configured to update the versioning information for the selected table in the change notification table when content in the selected data table is altered;

an act of caching interim results that can be used in the generation of a plurality of different Web based responses in a cache entry in the cache, the interim results based on one or more records from the selected data table, the cache entry made dependent on the selected data table by including the versioning information identifying and corresponding to the selected data table in the cache entry;

an act receiving a Web based request for a Web based response that is to include the interim results subsequent to caching the interim results in the cache entry, the web based request corresponding to a page file which includes one or more scripts to be executed to determine the Web based response;

an act of querying the change notification table for versioning information identifying and corresponding to the selected data table;

an act of receiving current versioning information identifying and corresponding to the selected data table;

an act of comparing the cached versioning information to the current versioning information;

an act of determining how to access the interim results for inclusion in a Web based response based on the results of comparing the versioning information and in response to receiving the Web based request for the portion of content, the act comprising an act of determining that the interim results are to be reconstructed from the one or more records in the selected data table and one or more records in one or more other data tables;

an act of accessing the interim results in accordance with the determination, the act comprising an act of reconstructing the interim results from one or more records in the selected data table and the one or more records in the one or more other data tables not withstanding that interim results were cached at the computer system when the Web based request was received;

an act of executing commands included in the one or more scripts in the page file to construct a Web based response responsive to the Web based request based on the interim results; and

based on the commands executed during the construction of the Web based response;

an act of assigning a database cache dependency to at least a portion of the constructed Web based response, the executed commands defining at least one database and at least one table on which the database cache entry is dependent, and further defining the portion of the constructed Web based response on which the cache dependency is dependent; and

an act of caching at least a portion of the constructed Web based response in a cache entry in the cache.

2. The method as recited in claim 1, wherein the act of selecting a data table that is to be monitored for content changes comprises an act of receiving user-input that causes the computer system to select a data table is to be monitored for content changes.

3. The method as recited in claim 1, wherein the act of selecting a data table that is to be monitored for content changes comprises an act of the computer system automatically selecting a data table in response to a received Web request.

4. The method as recited in claim 1, wherein the act of inserting a record for the selected data table into a change notification table compromises an act of inserting the record in response to user-input.

5. The method as recited in claim 1, wherein the act of inserting a record for the selected data table into a change notification table compromises an act of the computer system automatically inserting the record in response to a Web request.

6. The method as recited in claim 1, wherein the act of inserting a record for the selected data table into a change notification table compromises an act of inserting the record into a SQL table.

7. The method as recited in claim 1, wherein the act of assigning a trigger to the selected data table comprises an act of receiving user input instructing a trigger to be assigned to the selected data table.

8. The method as recited in claim 1, wherein the act of assigning a trigger to the selected data table comprises an act of the computer system automatically assigning a trigger in response to receiving a Web request for content contained in the selected data table.

9. The method as recited in claim 1, wherein the act of assigning a trigger to the selected data table comprises an act of the assigning a trigger that, when executed by a processing unit at the computer system in response to content in the selected data table being altered, will update a corresponding change ID in the table change notification table.

10. The method as recited in claim 1, wherein the act of receiving current versioning information identifying and corresponding to the selected data table comprises an act of receiving updated versioning information that indicates the cache entry is to be invalidated.

14. The method as recited in claim 1, further comprising:

an act of invalidating the cache entry that includes the interim results based on the comparison of version information in response to receiving the Web based request for the portion of content.

15. The method as recited in claim 1, wherein the act of determining how to access the interim results comprises an act of determining that the interim results are to be retrieved from the cache entry.

16. The method as recited in claim 1, wherein the act of constructing a Web based response responsive to the Web based request based on the interim results comprises including the reconstructed interim results in the Web based response not withstanding that the interim results were cached at the computer system when the Web based request was received.

21. The method as recited in claim 1, wherein a plurality of cache entries in the cache are made dependent on a single selected data table by including the versioning information identifying and corresponding to the selected data table each of the plurality of cache entries.

22. The method as recited in claim 1, wherein a record in the separate change notification table is removed.

23. The method as recited in claim 22 wherein when a record in the separate change notification table corresponding to a selected data table is removed, a plurality of cache entries that have been made dependent on the selected data table referenced by the record are also removed.

11. A computer program product executed at a computer system that access a database having one or more data tables, the computer system configured to provide content from the database to a Web server for inclusion in Web based responses to requests for Web based content, the computer system including a cache configured to cache database content included in Web based responses so as to provide more efficient access to the cached database content when formulating subsequent Web based responses, the computer program product implementing a method for formulating and caching a Web based response in response to receiving a Web based request for database content, the computer program product comprising one or more computer-readable storage media having stored thereon computer executable instructions that, when executed by a processing unit, implement the method including the following:

determine whether a cache entry for Web based content requested by a request for Web based content already exists in the cache; and

upon determining that the cache entry does not exist, perform the following;

select a data table that is to be monitored for data changes, the selected data table selected from among the one or more data tables of the database;

insert a record for the selected data table into a change notification table, the record including versioning information identifying and corresponding to the selected data table, the versioning information retrievable by the Web server to determine when a corresponding cache entry containing cacheable content from the selected data table is invalid;

assign a trigger to the selected data table, the trigger configured to update the versioning information for the selected table in the change notification table when data in the selected data table is altered;

cache interim results that can be used in the generation of a plurality of different Web based responses in a cache entry in the cache, the interim results based on one or more records from the selected data, the cache entry made dependent on the selected data table by including the versioning information identifying and corresponding to the selected data table in the cache entry;

receive a Web based request for a Web based response that is to include the interim results subsequent to caching the interim results in the cache entry, the web based request corresponding to a page file which includes one or more scripts to be executed to determine the Web based response;

query the change notification table for versioning information identifying and corresponding to the selected data table;

receive current versioning information identifying and corresponding to the selected data table,

compare the cached versioning information to the current versioning information;

determine how to access the interim results for inclusion in a Web based response based on the results of comparing the versioning information in response to receiving the Web based request for the portion of content by determining that the interim results are to be reconstructed from the one or more records in the selected data table and one or more records in one or more other data tables;

access the interim results in accordance with the determination by reconstructing the interim results from one or more records in the selected data table and the one or more records in the one or more other data tables not withstanding that interim results were cached at the computer system when the Web based request was received;

execute commands included in the one or more scripts in the page file to construct a Web based response responsive to the Web based request based on the interim results; and

based on the commands executed during the construction of the Web response;

assign a database cache dependency to at least a portion of the constructed Web based response, the executed commands defining at least one database and at least one table on which the database cache entry is dependent, and further defining the portion of the constructed Web based response on which the cache dependency is dependent; and

cache at least a portion of the constructed Web based response in a cache entry in the cache.

12. The computer program product as recited in claim 11, wherein the one or more computer-readable storage media are physical media.

13. The computer program product as recited in claim 11, wherein the one or more computer-readable storage media include system memory.

17. The computer program product as recited in claim 11, further comprising:

computer executable instructions that, when executed, cause the computer system to invalidate the cache entry that includes the interim results based on the comparison of version information in response to receiving the Web based request for the portion of content.

18. The computer program product as recited in claim 11, wherein computer executable instructions that, when executed, cause the computer system to determine how to access the interim results comprise computer executable instructions that, when executed, cause the computer system to an act of determine that the interim results to be retrieved from the cache entry.

19. The computer program product as recited in claim 11, wherein computer executable instructions that, when executed, cause the computer system to construct a Web based response based on the interim results comprise computer executable instructions that, when executed, cause the computer system to include a portion of content from the selected database table in the Web based response not withstanding that the interim results were cached at the computer system when the Web based request was received.

20. In a computer system that accesses a database having one or more data tables, the computer system configured to provide content from the database to a Web server for inclusion in Web based responses to requests for Web based content, the computer system including a cache configured to cache database content included in Web based responses so as to provide more efficient access to the cached database content when formulating subsequent Web based responses, a method for updating a database dependent cache entry and invalidating a key dependent cache entry that has a key dependency the database dependent cache entry when changes are detected in a data table, the method comprising the following:

an act of selecting a data table that is to be monitored for content changes, the selected data table selected from among the one or more data tables of the database;

an act of inserting a record for the selected data table into a separate change notification table, the record including versioning information identifying and corresponding to the selected data table, the versioning information retrievable by the Web server to determine when a corresponding cache entry containing cacheable content from the selected data table is invalid;

an act of attaching a trigger to the selected data table, the trigger configured to update the versioning information for the selected table in the change notification table when any record in the selected data table is altered regardless of the mechanism used to alter the record;

an act of constructing first interim results from a collection of records, including a plurality of records in the selected data table and one or more records from one or more other data tables, the first interim results usable in the generation of a plurality of different Web based responses;

an act of constructing second interim results from a collection of records, including a plurality of records in the selected data table and one or more records from one or more other data tables, the second interim results usable in the generation of a plurality of different Web based responses and dependent on the first interim results;

an act of caching the first interim results in a database dependent cache entry in the cache, the database dependent cache entry including the versioning information identifying and corresponding to the selected data table;

an act of caching the second interim results in a key dependent cache entry in the cache, the key dependent cache entry including a key dependency to the database dependent cache entry, the key dependency indicating that the key dependent cache entry should be invalidated by the database dependent cache entry when a change to a record in the selected data table is detected;

an act of a cache interface module issuing a blocking querying to the change notification table for versioning information identifying and corresponding to the selected data table, the blocking query waiting until versioning information for the selected table is updated before returning the versioning information for the selected data table;

an act of detecting a change to a record in the selected data table, subsequent to issuing the blocking query;

an act of the assigned trigger updating the versioning information for the selected table in the change notification table, subsequent to issuing the blocking query;

an act of the cache interface module receiving the updated versioning information in response to the blocking query;

an act of comparing the cached versioning information in the database dependent cache entry to the updated versioning information; and

based on the results of the comparison;

an act of the database dependent cache entry invalidating the key dependent cache entry for the second interim results; and

an act of updating the cached versioning information in the database dependent cache entry for the first interim results with the updated versioning information.