Optimizing Database Queries

• US 20090281992A1
• Filed: 05/08/2008
• Published: 11/12/2009
• Est. Priority Date: 05/08/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hu, Wei, Passe, Andrew P., Kethireddy, Shantan, Bestgen, Robert J., Thiemann, Ulrich
• Granted Patent: US 7,941,426 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 9/5027 the resource being a machine, e.g. CPUs, Servers, Terminals
• CPC Class Codes: G06F 9/5094 where the allocation takes into account power or heat criteria power management in computers in general G06F1/3203; thermal management in computers in general G06F1/206
• 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 optimizing database queries for energy efficiency. In general, a query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. In one embodiment, the query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Claims

1. A computer-implemented method, comprising:

receiving a database query;

generating a plurality of query plans for executing the database query;

estimating one or more characteristics of energy consumption requirements for executing each query plan; and

selecting, based at least on the one or more characteristics of energy consumption requirements, one of the plurality of query plans for executing the database query.

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

executing the selected query plan; and

returning a set of query results.

3. The computer-implemented method of claim 1, wherein estimating the one or more characteristics is based on predefined standards of energy consumption requirements for computer components.

4. The computer-implemented method of claim 1, wherein the one or more characteristics comprise the electrical energy required to spin one or more hard-disk drives required to execute each query plan.

5. The computer-implemented method of claim 1, wherein the one or more characteristics comprise the electrical energy required to cool one or more hard-disk drives required to execute each query plan.

6. The computer-implemented method of claim 1, wherein the one or more characteristics comprise the electrical energy required to cool one or more processors required to execute each query plan.

7. The computer-implemented method of claim 1, further comprising estimating one or more characteristics of time requirements for executing each query plan, and wherein selecting a query plan is further based on the one or more characteristics of time requirements.

8. The computer-implemented method of claim 7, wherein selecting a query plan is further based on a user-specified setting indicating a relative priority between energy requirements and time requirements.

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

receiving a database query;

generating a plurality of query plans for executing the database query;

estimating one or more characteristics of energy consumption requirements for executing each query plan; and

selecting, based at least on the one or more characteristics of energy consumption requirements, one of the plurality of query plans for executing the database query.

10. The computer readable storage medium of claim 9, the operation further comprising:

executing the selected query plan; and

returning a set of query results.

11. The computer readable storage medium of claim 9, wherein estimating the one or more characteristics is based on predefined standards of energy consumption requirements for computer components.

12. The computer readable storage medium of claim 9, wherein the one or more characteristics comprise the electrical energy required to spin one or more hard-disk drives required to execute each query plan.

13. The computer readable storage medium of claim 9, wherein the one or more characteristics comprise the electrical energy required to cool one or more hard-disk drives required to execute each query plan.

14. The computer readable storage medium of claim 9, wherein the one or more characteristics comprise the electrical energy required to cool one or more processors required to execute each query plan.

15. The computer readable storage medium of claim 9, further comprising estimating one or more characteristics of time requirements for executing each query plan, and wherein selecting a query plan is further based on the one or more characteristics of time requirements.

16. The computer readable storage medium of claim 15, wherein selecting a query plan is further based on a user-specified setting indicating a relative priority between energy requirements and time requirements.

17. 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 a database query;

generating a plurality of query plans for executing the database query;

estimating one or more characteristics of energy consumption requirements for executing each query plan; and

selecting, based at least on the one or more characteristics of energy consumption requirements, one of the plurality of query plans for executing the database query.

18. The system of claim 17, the operation further comprising:

executing the selected query plan; and

returning a set of query results.

19. The system of claim 17, wherein the one or more characteristics comprise the electrical energy required to spin one or more hard-disk drives required to execute each query plan.

20. The system of claim 17, wherein the one or more characteristics comprise the electrical energy required to cool one or more hard-disk drives required to execute each query plan.