Grouping predicted database queries

• US 8,015,202 B2
• Filed: 06/19/2008
• Issued: 09/06/2011
• Est. Priority Date: 06/19/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hu, Wei, Bestgen, Robert Joseph, Kethireddy, Shantan, Thiemann, Ulrich, Passe, Andrew Peter
• Primary Examiner(s): Mofiz; Apu M
• Assistant Examiner(s): Nguyen; Cindy
• Application Number:
  US12/142,206
• Publication Number:
  US 20090319475A1
• Time in Patent Office: 1,174 Days
• Field of Search: 707/769, 707/799, 707718-720
• US Class Current: 707/769
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: Y02D 10/00 Energy efficient computing, e.g. low power processors, power management or thermal management

Abstract

Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Claims

1. A computer-implemented method, comprising:

receiving at least one query for a database;

determining data elements required to execute the at least one query;

matching, by operation of one or more computer processors, the received at least one query to a pattern of previously executed queries;

predicting, based on the matching pattern, attributes of one or more queries to be subsequently received;

determining, based on the predicted attributes, data elements required to execute the one or more subsequent queries;

determining a grouping of the one or more subsequent queries with the at least one received query, such that each query in the grouping will require one or more data elements stored in a common storage device in order to be executed, the grouping being performed on the basis of an objective to reduce energy consumption;

receiving the one or more subsequent queries; and

adding to a queue, according to the determined grouping, the one or more subsequent queries with the at least one received query.

2. The computer-implemented method of claim 1, wherein the one or more data elements are selected from:

(i) a table of a database and (ii) an index of a database.

3. The computer-implemented method of claim 1, further comprising:

determining an order for dispatching the queued queries for execution; and

dispatching the queued queries according to the determined order.

4. The computer-implemented method of claim 3, further comprising:

retrieving the determined one or more data elements from the common storage device; and

executing the dispatched queries.

5. The computer-implemented method of claim 1, wherein matching the received at least one query to a pattern of previously executed queries comprises analyzing a history of query plans previously executed on the database.

6. The computer-implemented method of claim 1, wherein the common storage device is a hard-disk drive.

7. The computer-implemented method of claim 6, wherein the objective to reduce energy consumption is directed to minimizing a number of times the hard-disk drive spins up to a predefined read speed.

8. A computer readable storage medium containing a program which, when executed, performs an operation, comprising:

receiving at least one query for a database;

determining data elements required to execute the at least one query;

matching the received at least one query to a pattern of previously executed queries;

predicting, based on the matching pattern, attributes of one or more queries to be subsequently received;

determining, based on the predicted attributes, data elements required to execute the one or more subsequent queries;

determining a grouping of the one or more subsequent queries with the at least one received query, such that each query in the grouping will require one or more data elements stored in a common storage device in order to be executed, the grouping being performed on the basis of an objective to reduce energy consumption;

receiving the one or more subsequent queries; and

adding to a queue, according to the determined grouping, the one or more subsequent queries with the at least one received query.

9. The computer readable storage medium of claim 8, wherein the one or more data elements are selected from:

(i) a table of a database and (ii) an index of a database.

10. The computer readable storage medium of claim 8, further comprising:

determining an order for dispatching the queued queries for execution; and

dispatching the queued queries according to the determined order.

11. The computer readable storage medium of claim 10, further comprising:

retrieving the determined at least one data element from the common storage device; and

executing the dispatched queries.

12. The computer readable storage medium of claim 8, wherein matching the received at least one query to a pattern of previously executed queries comprises analyzing a history of query plans previously executed on the database.

13. The computer readable storage medium of claim 8, wherein the common storage device is a hard-disk drive.

14. The computer readable storage medium of claim 13, wherein the objective to reduce energy consumption is directed to minimizing a number of times the hard-disk drive spins up to a predefined read speed.

15. A system, comprising:

a database;

a processor; and

a memory containing a program, which when executed by the processor is configured to perform an operation, comprising;

receiving at least one query for a database;

determining data elements required to execute the at least one query;

matching the received at least one query to a pattern of previously executed queries;

predicting, based on the matching pattern, attributes of one or more queries to be subsequently received;

determining, based on the predicted attributes, data elements required to execute the one or more subsequent queries;

determining a grouping of the one or more subsequent queries with the at least one received query, such that each query in the grouping will require one or more data elements stored in a common storage device in order to be executed, the grouping being performed on the basis of an objective to reduce energy consumption;

receiving the one or more subsequent queries; and

adding to a queue, according to the determined grouping, the one or more subsequent queries with the at least one received query.

16. The system of claim 15, wherein the one or more data elements are

selected from:

(i) a table of a database and (ii) an index of a database.

17. The system of claim 15, further comprising:

determining an order for dispatching the aggregated queries for execution; and

dispatching the aggregated queries according to the determined order.

18. The system of claim 17, further comprising:

retrieving the determined one or more data elements from the common storage device; and

executing the dispatched queries.

19. The system of claim 15, wherein matching the received at least one query to a pattern of previously executed queries comprises analyzing a history of query plans previously executed on the database.

20. The system of claim 15, wherein the common storage device is a hard-disk drive.

21. The system of claim 20, wherein the objective to reduce energy consumption is directed to minimizing a number of times the hard-disk drive spins up to a predefined read speed.