DYNAMIC QUERY OPTIMIZATION WITH PILOT RUNS

• US 20150363466A1
• Filed: 06/11/2014
• Published: 12/17/2015
• Est. Priority Date: 06/11/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Balmin, Andrey, Ercegovac, Vuk, Kutsch, Marcel, Ozcan, Fatma, Jackson, Jesse E., Karanasos, Konstantinos, Xia, Chunyang
• Granted Patent: US 9,836,506 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination

Abstract

In one embodiment, a computer-implemented method includes selecting one or more sub-expressions of a query during compile time. One or more pilot runs are performed by one or more computer processors. The one or more pilot runs include a pilot run associated with each of one or more of the selected sub-expressions, and each pilot run includes at least partial execution of the associated selected sub-expression. The pilot runs are performed during execution time. Statistics are collected on the one or more pilot runs during performance of the one or more pilot runs. The query is optimized based at least in part on the statistics collected during the one or more pilot runs, where the optimization includes basing cardinality and cost estimates on the statistics collected during the pilot runs.

Claims

1. A computer-implemented method, comprising:

selecting one or more sub-expressions of a query during compile time;

performing, by one or more computer processors, one or more pilot runs, comprising a pilot run associated with each of one or more of the selected sub-expressions, wherein each pilot run comprises at least partial execution of the associated selected sub-expression, and wherein the pilot runs are performed during execution time;

collecting statistics on the one or more pilot runs during performance of the one or more pilot runs; and

optimizing the query based at least in part on the statistics collected during the one or more pilot runs, wherein the optimization comprises basing cardinality and cost estimates on the statistics collected during the pilot runs.

2. The method of claim 1, wherein performing the one or more pilot runs comprises performing two or more pilot runs in parallel across two or more processors.

3. The method of claim 1, further comprising storing, in a database metastore, the statistics collected during the one or more pilot runs, wherein the statistics collected during a first pilot run associated with a first selected sub-expression are stored in the database metastore in association with a first expression signature identifying the first selected sub-expression.

4. The method of claim 1, wherein the one or more selected sub-expressions comprise a first selected sub-expression, and wherein performing the one or more pilot runs comprises:

searching a database metastore for previous pilot-run data related to the first selected sub-expression; and

performing a first pilot run associated with the first selected sub-expression if the previous pilot run data related to the first selected sub-expression is not found in the database metastore.

5. The method of claim 4, wherein the one or more selected sub-expressions further comprise a second selected sub-expression, and wherein performing the one or more pilot runs comprises:

searching the database metastore for previous pilot-run data related to the second selected sub-expression; and

opting not to perform a pilot run associated with the second selected sub-expression if the previous pilot run data related to the second selected sub-expression is found in the database metastore.

6. The method of claim 1, wherein selecting the one or more sub-expressions of the query comprises selecting at least one of a user-defined function and a sub-expression involving a relation greater than a predetermined size in the query.

7. The method of claim 1, wherein selecting the one or more sub-expressions of the query comprises selecting a join in the query.

8. A system comprising:

a pilot-run unit configured to;

select one or more sub-expressions of a query during compile time; and

perform, by one or more computer processors, one or more pilot runs, comprising a pilot run associated with each of one or more of the selected sub-expressions, wherein each pilot run comprises at least partial execution of the associated selected sub-expression, and wherein the pilot runs are performed during execution time;

a statistics unit configured to collect statistics on the one or more pilot runs during performance of the one or more pilot runs; and

a cost-based optimizer configured to optimize the query based at least in part on the statistics collected during the one or more pilot runs, wherein the optimization comprises basing cardinality and cost estimates on the statistics collected during the pilot runs.

9. The system of claim 8, wherein the pilot-run unit is configured to perform the one or more pilot runs by performing two or more pilot runs in parallel across two or more processors.

10. The system of claim 8, wherein the statistics unit is further configured to store, in a database metastore, the statistics collected during the one or more pilot runs, wherein the statistics collected during a first pilot run associated with a first selected sub-expression are stored in the database metastore in association with a first expression signature identifying the first selected sub-expression.

11. The system of claim 8, wherein the one or more selected sub-expressions comprise a first selected sub-expression, and wherein the pilot-run unit is configured to perform the one or more pilot runs by:

searching a database metastore for previous pilot-run data related to the first selected sub-expression; and

performing a first pilot run associated with the first selected sub-expression if the previous pilot run data related to the first selected sub-expression is not found in the database metastore.

12. The system of claim 11, wherein the one or more selected sub-expressions further comprise a second selected sub-expression, and wherein the pilot-run unit is configured to perform the one or more pilot runs by:

searching the database metastore for previous pilot-run data related to the second selected sub-expression; and

opting not to perform a pilot run associated with the second selected sub-expression if the previous pilot run data related to the second selected sub-expression is found in the database metastore.

13. The system of claim 8, wherein the pilot-run unit is configured to select the one or more sub-expressions of the query by selecting at least one of a user-defined function and a sub-expression involving a relation greater than a predetermined size in the query.

14. A computer program product for optimizing a query, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by a processor to cause the processor to perform a method comprising:

selecting one or more sub-expressions of a query during compile time;

performing one or more pilot runs, comprising a pilot run associated with each of one or more of the selected sub-expressions, wherein each pilot run comprises at least partial execution of the associated selected sub-expression, and, wherein the pilot runs are performed during execution time;

collecting statistics on the one or more pilot runs during performance of the one or more pilot runs; and

optimizing the query based at least in part on the statistics collected during the one or more pilot runs, wherein the optimization comprises basing cardinality and cost estimates on the statistics collected during the pilot runs.

15. The computer program product of claim 14, wherein performing the one or more pilot runs comprises performing two or more pilot runs in parallel across two or more processors.

16. The computer program product of claim 14, the method further comprising storing, in a database metastore, the statistics collected during the one or more pilot runs, wherein the statistics collected during a first pilot run associated with a first selected sub-expression are stored in the database metastore in association with a first expression signature identifying the first selected sub-expression.

17. The computer program product of claim 14, wherein the one or more selected sub-expressions comprise a first selected sub-expression, and wherein performing the one or more pilot runs comprises:

searching a database metastore for previous pilot-run data related to the first selected sub-expression; and

performing a first pilot run associated with the first selected sub-expression if the previous pilot run data related to the first selected sub-expression is not found in the database metastore.

18. The computer program product of claim 17, wherein the one or more selected sub-expressions further comprise a second selected sub-expression, and wherein performing the one or more pilot runs comprises:

searching the database metastore for previous pilot-run data related to the second selected sub-expression; and

opting not to perform a pilot run associated with the second selected sub-expression if the previous pilot run data related to the second selected sub-expression is found in the database metastore.

19. The computer program product of claim 14, wherein selecting the one or more sub-expressions of the query comprises selecting at least one of a user-defined function and a sub-expression involving a relation greater than a predetermined size in the query.

20. The computer program product of claim 14, wherein selecting the one or more sub-expressions of the query comprises selecting a join in the query.