Efficient aggregate summary views of massive numbers of items in highly concurrent update environments

• US 20050125325A1
• Filed: 12/07/2004
• Published: 06/09/2005
• Est. Priority Date: 12/08/2003
• Status: Abandoned Application

• Current Assignee: Savi Technology Incorporated
• Original Assignee: Savi Technology Incorporated
• Inventors: Alcock, Andrew E.S., Chai, Zhong Hua
• Application Number:
  US11/007,499
• Publication Number:
  US 20050125325A1
• Time in Patent Office: Days
• US Class Current: 705/35
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06Q 40/00 Finance; Insurance; Tax strategies; Processing of corporate or income taxes

Abstract

A method and system improves efficiency of highly concurrent aggregate summaries updates by delaying the updates to as late as possible in the transaction, while maintaining an accurate in-progress aggregate summary for use by transaction in progress. The system uses a temporary table to store updates to aggregate summaries and consolidates the temporary table with the aggregate summary to create a view of the accurate in-progress data for use by the transaction. Prior to the transaction commit, the system converts the contents of the temporary delta table into a single-statement consolidated update of the inventory summary table, reducing throughput delays caused by write locks early in the transaction.

Claims

1. A method of updating concurrent aggregate summaries, comprising:

receiving as the first step in a transaction a request to update data stored in a summary table;

applying the request to a delta table;

consolidating the summary and delta tables to create a view table; and

performing an update of the summary table using the contents of the delta table.

2. The method of claim 1, wherein the request is in response to a change in the state of an asset and the data is associated with the asset.

3. The method of claim 1, wherein performing an update of the summary table further comprises:

locking rows of the summary table associated with the transaction;

updating the summary table with data from the delta table; and

deleting the rows of the delta table.

4. The method of claim 1, wherein the view table is used for business logic associated with the transaction in progress and the summary table is used for business logic associated with other transactions.

5. The method of claim 4, wherein the business logic triggers processes associated with data in the view table.

6. The method of claim 1, wherein performing an update of the summary table happens near the end of the transaction.

7. The method of claim 1, wherein performing an update of the summary table happens just before the transaction commits.

8. The method of claim 1, wherein performing an update of the summary table comprises issuing a single SQL statement.

9. The method of claim 1, further comprising consolidating the delta table data prior to consolidating the summary and delta tables.

10. The method of claim 1, wherein applying the request to a delta table includes adding new rows to the delta table.

11. A computer program product for updating concurrent aggregate summaries, comprising:

a computer-readable medium; and

computer program code encoded on the medium for;

receiving as the first step in a transaction a request to update data stored in a summary table;

applying the request to a delta table;

consolidating the summary and delta tables to create a view table; and

performing an update of the summary table using the contents of the delta table.

12. The computer program product of claim 11, wherein the request is in response to a change in the state of an asset and the data is associated with the asset.

13. The computer program product of claim 11, wherein performing an update of the summary table further comprises:

locking rows of the summary table associated with the transaction;

updating the summary table with data from the delta table; and

inactivating the rows of the delta table.

14. The computer program product of claim 11, wherein the view table is used for business logic associated with the transaction in progress and the summary table is used for business logic associated with other transactions.

15. The computer program product of claim 14, wherein the business logic triggers processes associated with data in the view table.

16. The computer program product of claim 11, wherein performing an update of the summary table happens near the end of the transaction.

17. The computer program product of claim 11, wherein performing an update of the summary table happens just before the transaction commits.

18. The computer program product of claim 11, wherein performing an update of the summary table comprises issuing a single SQL statement.

19. The computer program product of claim 11, further comprising consolidating the delta table data prior to consolidating the summary and delta tables.

20. The computer program product of claim 11, wherein applying the request to a delta table includes adding new rows to the delta table.

21. A system for updating concurrent aggregate summaries, comprising:

a business engine for receiving as the first step in a transaction a request to update data stored in a summary table;

a transaction manager for initiating the transaction commit;

an inventory module for performing an update of the summary table; and

a database for storing table data.

22. The system of claim 21, the business engine further comprising an application module for applying the request to a delta table.

23. The system of claim 21, the inventory module further comprising:

an increment/decrement module for altering table data;

a view module for providing the transaction access to a view table; and

a consolidation module for consolidating the summary table and a delta table to create the view table and for performing an update of the summary table.

24. The system of claim 22, the consolidation module further configured for:

locking rows of the summary table associated with the transaction;

updating the summary table with data from the delta table; and

inactivating the rows of the delta table.