METHOD AND APPARATUS FOR PARALLEL EXECUTION OF SQL FROM STORED PROCEDURES

• US 20030014393A1
• Filed: 12/22/1999
• Published: 01/16/2003
• Est. Priority Date: 12/22/1999
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation (NCR Voyix Corporation)
• Inventors: NAG, BISWADEEP, KABRA, NAVIN, PATEL, JIGNESH M., CHEN, JIAN-JUN, YU, JIE-BING
• Granted Patent: US 6,507,834 B1
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 16/2443 Stored procedures
• CPC Class Codes: G06F 16/24532 of parallel queries
• 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/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A method, apparatus, and an article of manufacture for parallel execution of SQL operations from stored procedures. One or more embodiments of the invention provide the stored procedure (stored procedure) with a C++ class (hereinafter referred to as “dispatcher”) that can take an SQL query and start parallel execution of the query. The query is optimized and parallelized. The dispatcher executes the query, sets up the communication links between the various operators in the query, and ensures that all the results are sent back to the data-server that originated the query request. Further, the dispatcher merges the results of the parallel execution and produces a single stream of tuples that is fed to the calling stored procedure. To provide the single stream to the calling stored procedure, one or more embodiments of the invention utilize a class that provides the stored procedure with a simple and easy-to-use interface to access the results of the nested SQL execution.

Claims

1. A method of parallel execution of SQL in a stored procedure comprising:

obtaining an SQL command in a stored procedure on a data server;

transmitting at least a portion of the SQL command to a query coordinator;

the query coordinator generating an execution plan that provides for parallel execution of the portion of the SQL command;

transmitting at least a portion of the execution plan from the query coordinator to a plurality of dispatcher instances on a plurality of data servers; and

the plurality of dispatchers executing the transmitted portions of the execution plan on the plurality of data servers.

2. The method of claim 1 wherein the transmitting at least a portion of the SQL command, the transmitting at least a portion of the execution plan, and the query coordinator are transparent to the stored procedure.

3. The method of claim 1 further comprising:

one or more of said plurality of dispatchers merging results of the execution;

specifying an endpoint to retrieve the merged results; and

providing the stored procedure with a mechanism for retrieving the results in a stream from the specified endpoint.

4. The method of claim 3 wherein the input stream comprises one or more tuples.

5. The method of claim 1 wherein said generating further comprises:

a query optimizer generating an ASCII plan; and

a parallelizer generating a parallel execution plan;

6. A system for parallel execution of SQL in a stored procedure comprising:

a data server configured to transmit at least a portion of an SQL command;

a stored procedure on the data server, the stored procedure comprised of the SQL command;

a dispatcher on the data server configured to;

receive portions of an execution plan; and

execute the received portions of the execution plan on a plurality of the data servers;

a query coordinator configured to;

receive the at least a portion of the SQL command;

generate the execution plan that provides for parallel execution of the portion of the SQL command; and

transmit at least a portion of the execution plan to a plurality of the dispatchers on a plurality of the data servers.

7. The system of claim 6 wherein the query coordinator and the operations of the dispatcher are transparent to the stored procedure.

8. The system of claim 6 further comprising:

the dispatcher further configured to;

merge one or more results of execution of said execution plan; and

provide said results to a specified endpoint; and

the stored procedure further configured to retrieve the merged results in a stream from the specified endpoint.

9. The system of claim 8 wherein the input stream comprises one or more tuples.

10. The system of claim 6 further comprising:

a query optimizer in the query coordinator, the query optimizer configured to generate an ASCII plan from the portion of SQL; and

a parallelizer in the query coordinator, the parallelizer configured to generate a parallel execution plan from said ASCII plan.

11. An article of manufacture for parallel execution of SQL in a stored procedure comprising:

means for obtaining an SQL command in a stored procedure on a data server;

means for transmitting at least a portion of the SQL command to a query coordinator;

means for the query coordinator to generate an execution plan that provides for parallel execution of the portion of the SQL command;

means for transmitting at least a portion of the execution plan from the query coordinator to a plurality of dispatcher instances on a plurality of data servers; and

means for the plurality of dispatchers to execute the transmitted portions of the execution plan on the plurality of data servers.

12. The article of manufacture of claim 11 wherein the means for transmitting the portion of the SQL command, the means for transmitting at least a portion of the execution plan, and the query coordinator are transparent to the stored procedure.

13. The article of manufacture of claim 11 further comprising:

means for one or more of said plurality of dispatchers to merge results of the execution;

means for specifying an endpoint to retrieve the merged results; and

means for providing the stored procedure with a mechanism for retrieving the results in a stream from the specified endpoint.

14. The article of manufacture of claim 13 wherein the input stream comprises one or more tuples.

15. The article of manufacture of claim 11 wherein said means for generating further comprises:

means for a query optimizer to generate an ASCII plan; and

means for a parallelizer to generate a parallel execution plan;