SQL code generation for heterogeneous environment

• US 8,171,018 B2
• Filed: 10/06/2008
• Issued: 05/01/2012
• Est. Priority Date: 07/07/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: IBM International Group BV (International Business Machines Corporation)
• Inventors: Zane, Barry M., Ballard, James P., Hinshaw, Foster D., Yerabothu, Premanand
• Primary Examiner(s): PULLIAM, CHRISTYANN R
• Application Number:
  US12/287,152
• Publication Number:
  US 20090083219A1
• Time in Patent Office: 1,303 Days
• Field of Search: None
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 8/30 Creation or generation of source code
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

This invention relates generally to a system for processing database queries, and more particularly to a method for generating high level language or machine code to implement query execution plans. In one preferred embodiment, the method begins by receiving a subject query, and then forming an execution plan corresponding to the subject query. The execution plan will typically have a sequence of component snippets or pieces and corresponding processes for implementing the pieces. For at least one piece in the plan, the process then (a) generates source code using different code generation techniques as a function of expected runtime processing machine types; and then (b) compiles the generated source code to form machine executable code for implementing the subject query. As a result, the query executed directly as machine executable code thereby avoiding runtime interpretation of the pieces in the execution plan.

Claims

1. A method for generating machine executable code for implementing a query of a database, the database having tables and records of data, comprising the steps of:

receiving a subject query;

forming an execution plan corresponding to the subject query, the execution plan having a sequence of pieces and corresponding processes for implementing the pieces; and

for at least one piece of the plan, (a) generating source code corresponding to the at least one piece of the plan using at least one of a plurality of different code generation techniques being selected as a function of an expected processing machine type of a plurality of expected processing machine types; and

(b) compiling the generated source code to form machine executable code for implementing the subject query, said generated source code avoiding runtime interpretation of the pieces in the execution plan,

wherein the code generated for at least one piece of the plan is cached in a centralized location to remain available to be used in processing a subsequent plan piece without rerunning the generating step.

2. A method as in claim 1 additionally comprising:

delivering the compiled machine executable code to a selected one of the processing machine types for execution.

3. A method as in claim 2 wherein the delivered machine executable code is run on the identified processing machine types to complete the query.

4. A method as in claim 1 wherein the expected processing machine type is two or more machine types to provide a heterogeneous code execution environment.

5. A method as in claim 1 additionally comprising

generating linkage information for the generated machine code.

6. A method as in claim 1 wherein the generated machine code is in a C language.

7. A method as in claim 1 wherein there are multiple expected processing machine types for executing at least one piece of the database query, and the generated machine code runs on the multiple machine types in parallel.

8. A method as in claim 1 wherein the code generation step is performed for the subsequent piece without recompiling the code.

9. A method as in claim 1 wherein the query language specifies arithmetic operations at a query language precision level, and wherein the generating step generates only instructions that are carried out using native data types of the target processing machine types.

10. A method as in claim 1 wherein the target processing machines have different numbers of general purpose registers (GPRs), and the compiling step tracks register usage differently for the different target processing machines.

11. A method as in claim 1 wherein the query language specifies arithmetic operations at a query language precision level, and wherein the generating step generates instructions carried out at a precision level determined from statistics of data stored in the database.

12. A method as in claim 1 wherein the generated source code includes human readable code containing comments for later analysis.

13. A method as in claim 1 wherein the generated code includes human readable source code having variable names that follow a predefined convention.

14. A method for generating machine executable code for implementing a query of a database, the database having tables and records of data, comprising the steps of:

receiving a subject query;

forming an execution plan corresponding to the subject query, the execution plan having a sequence of pieces and corresponding processes for implementing the pieces; and

for at least several identified pieces of the plan, (a) generating source code corresponding to the several identified pieces of the plan, with the source code generated for at least one identified subsequent piece of the plan being dependent upon the results of code generated for a another identified prior piece of the plan to be executed prior to the identified piece of the plan, wherein the source code generation uses at least one of a plurality of different code generation techniques being selected as a function of an expected processing machine type of a plurality of expected processing machine types; and

(b) compiling the generated source code to form machine executable code for implementing the subject query, said generated source code avoiding runtime interpretation of the at least several pieces in the execution plan.

15. A method as in claim 14 wherein the code for the subsequent piece of the plan is not generated until after execution of the prior piece of the plan.

16. A method as in claim 14 wherein the code for the subsequent piece of the plan is not generated until a prior piece of the plan is executing.

17. A method as in claim 14 wherein the code for the subsequent piece of the plan is not generated until a run time for the piece occurs.

18. A method as in claim 17 wherein the delivered machine executable code is run on the identified processing machine types to complete the query.

19. A method as in claim 14 wherein the expected processing machine type is two or more machine types to provide a heterogeneous code execution environment.

20. A method as in claim 14 further including delivering the compiled machine executable code to a selected one of the processing machine types for execution.

21. A system for processing a query on a database comprising:

at least one computer having a memory;

a plurality of database operation processor components, with at least two of the database operation processor components having different operation processing capabilities, such that the database operation processor components provide at least two different locales for executing database operations, with the two locales having different operation processing capabilities and such that more than one database operation processor component locale is executing a given selected database operation;

a database catalog comprising database table information, database operation processor component locale information, and database operation processor characteristic information;

a query optimizer receiving the query and accessing the database catalog to develop a query execution plan, the query execution plan defining two or more plan pieces that include database operations to carry out the query, and the query execution plan also defining a respective database operation processor component locale at which to carry out each such database operation in the query execution plan, by comparing the processing capabilities of the different locale types for the database operation; and

for at least one piece of the query execution plan, (a) generating source code corresponding to the at least one piece of the plan using at least one of a plurality of different code generation techniques being selected as a function of specified locale types of a plurality of specified locale types; and

(b) compiling the generated source code to form machine executable code for implementing the query execution plan, said generated source code avoiding runtime interpretation of the query execution plan pieces.

22. A computer program product for generating machine executable code for implementing a query of a database, the database having tables and records of data, comprising:

a non-transitory computer readable storage medium having computer readable program code embodied therewith, the computer readable program code configured to;

receive a subject query;

form an execution plan corresponding to the subject query, the execution plan having a sequence of pieces and corresponding processes for implementing the pieces; and

for at least several identified pieces of the plan, (a) generate source code corresponding to the several identified pieces of the plan, with the source code generated for at least one identified subsequent piece of the plan being dependent upon the results of code generated for a another identified prior piece of the plan to be executed prior to the identified piece of the plan, wherein the source code generation uses at least one of a plurality of different code generation techniques being selected as a function of an expected processing machine type of a plurality of expected processing machine types; and

(b) compile the generated source code to form machine executable code for implementing the subject query, said generated source code avoiding runtime interpretation of the at least several pieces in the execution plan.

23. A computer program product as in claim 22 wherein the code for the subsequent piece of the plan is not generated until after execution of the prior piece of the plan.

24. A computer program product as in claim 22 wherein the code for the subsequent piece of the plan is not generated until a prior piece of the plan is executing.

25. A computer program product as in claim 22 wherein the code for the subsequent piece of the plan is not generated until a run time for the piece occurs.