1. Field of the Invention
The present invention relates to systems and methods for performing queries on data stored in a database, and in particular to a method and system for executing SQL from stored procedures.
2. Description of the Related Art
The ability to manage massive amounts of information has become a virtual necessity in business today. The information and data are often stored in related files. A set of related files is referred to as a database. A database management system DBMS) creates and manages one or more databases. Today, DBMSs can manage any form of data including text, images, sound and video. Further, large-scale integrated DBMS"" provide an efficient, consistent, and secure means for storing and retrieving the vast amounts of data.
Certain computer languages have been developed and utilized to interact with and manipulate the data. For example, SQL (Structured Query Language) is a language used to interrogate and process data in a relational database (a database in which relationships are established between files and information stored in the database). Originally developed for mainframes, most database systems designed for client/sever environments support SQL. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Thus, methods and functions may embed and utilize SQL commands.
Stored Procedures
Users/programmers often need to invoke the same set of commands (or the same set of commands with different parameters) at varying times and locations of a program. In such a situation, the query may be placed into a stored procedure. A stored procedure is a batch of SQL statements stored in a database/on a server, that may be partially or fully processed/compiled before it is stored (or upon its first invocation). Additionally, a stored procedure is a method or procedure written in any programming language that is partially or fully processed/compiled before it is stored (or upon its first invocation).
Stored procedures may be called directly from a client or from a database trigger and are often stored on the server. A database trigger is a user defined mechanism that causes a stored procedure to automatically initiate and execute upon the occurrence of the user specified events in the database (i.e., when the trigger xe2x80x9cfiresxe2x80x9d). Thus, the trigger will not xe2x80x9cfirexe2x80x9d unless the event(s) specified by the user occurs. For example, a user may define a trigger to automatically xe2x80x9cfirexe2x80x9d whenever a user updates, deletes, or inserts data.
Since the stored procedure is stored on a server, the stored procedure is available to all clients and does not need to be replicated in each client. Further, by storing the stored procedure on the server, when the stored procedure is modified, all clients automatically get/have access to the new version. This saves programming effort especially when different client user interfaces and development systems are used. Further, this allows stored procedures to be an easy mechanism for sharing complex queries and functions between multiple applications. Additionally, SQL and stored procedures may call other stored procedures and may be written independently from (and without knowledge of) the underlying DBMS.
A stored procedure may be partially or completely processed/compiled before it is stored on the database. Consequently, the stored procedure does not have to be parsed and compiled each time it is invoked. Further, because a stored procedure is stored in a compiled format, it executes faster than if its constituent commands were executed individually.
Alternatively, a stored procedure may not be compiled prior to storage but may be automatically compiled the first time the procedure is invoked. As part of such a compilation, a query execution plan may be generated. The query execution plan describes the order in which tables are to be accessed and the indexes to be used. Further, the query execution plan is optimized for the stored procedure parameters and data in the database tables at the time the stored procedure is first executed.
A stored procedure may be invoked by its name. The caller can pass parameters to and receive results from the stored procedure. A user can create and name a stored procedure to execute specific database queries and perform other database tasks. For example, a user may create a stored procedure that returns the number of videos of a particular movie remaining in a video store for the video title that is specified at the time the stored procedure is called.
Stored procedures may also maintain the integrity of the database and prevent unauthorized users from modifying certain entries. For example, a user may be given the right to call a stored procedure that updates a table or set of tables but denied the right to update the tables directly.
Stored procedures may be created using a variety of mechanisms. The following format may be utilized to declare a stored procedure:
For example the following stored procedure called myproc will return the number of Casablanca videos left in a video store as well as other movie titles, the rental price of those movie titles, and the location of those videos when Humphrey Bogart is an actor in the movie:
As described above, stored procedures can also be passed parameters. Parameters may be defined as part of the stored procedure creation statement. The syntax of a xe2x80x9ccreate procxe2x80x9d command with parameters is:
For example, the following stored procedure may be passed the @mytitle and @myactor parameters for use in the select query:
Once a stored procedure has been created, a user can invoke the stored procedure using the following syntax:
In the above syntax, xe2x80x9cdatabasexe2x80x9d and xe2x80x9cownerxe2x80x9d will default to the current database and the current dbo (database owner). For example, the following command may invoke the stored procedure xe2x80x9cmyprocxe2x80x9d defined above:
In this example, the user would see the same results as if the following command were utilized:
Additionally, the xe2x80x9cexecxe2x80x9d portion of an invocation of a stored procedure is not necessary if the stored procedure call is the first line in a batch.
Stored procedures can also have a xe2x80x9creturnxe2x80x9d status. A xe2x80x9creturnxe2x80x9d statement returns from the stored procedure with an optional status parameter. The return status is zero for success, or negative otherwise. Negative values between xe2x88x921 and xe2x88x9299 are reserved. For example, the following stored procedure returns a negative value (xe2x88x92999) if no rows/records are in the result set and a 0 if rows/records are in the result set:
The following commands illustrate the invocation of the above stored procedure with a return status:
In addition to providing parameters when invoking a stored procedure, default values for the parameters may be specified:
The above stored procedure provides a default value of 0 for @myparam1, a default value of null for @myparam2, and a default value of xe2x80x9cmydefaultxe2x80x9d for @myparam3. To invoke the stored procedure, the user may specify the values for one or more parameters, if desired, or may allow one or more of the default values to be utilized:
In the above invocation, only @myparam1 is specified in the execution line. Consequently, the specified default values for @myparam2 and @myparam3 are utilized (i.e., null and xe2x80x9cmydefaultxe2x80x9d).
Additionally, stored procedures can have output parameters as demonstrated below:
The output for the above stored procedure is the number of rows (i.e., the count) in the titles table where the value of x is greater than @myparam1.
To further optimize the processing time for working with and manipulating the data, some DBMS have distributed the data and provided for parallel processing of and to the data. Thus, the stored procedures utilized to manipulate and work with the data are executed in parallel on the parallelized/distributed data. Some stored procedures are associated directly with certain types of data on a particular data server (storage location for the data). However, these stored procedures may attempt to manipulate and retrieve information from data not located on the data server where the stored procedure is located. Accordingly, it is difficult to start up parallel execution of a stored procedure that resides on any one data server.
Further, since the stored procedures may be written independently from (and without knowledge of the parallelized data system, it is difficult to provide results to the stored procedure in a clean manner. In other words, when a stored procedure operates or requests data (i.e., using SQL commands), the interface within which the results are returned is difficult to establish and maintain without exposing the parallelism to the stored procedure. What is needed is a system and method for efficiently and cleanly executing SQL statements from stored procedures on a parallelized DBMS.
To address the requirements described above, the present invention discloses a method, apparatus, and an article of manufacture for parallel execution of SQL operations from stored procedures.
The method comprises providing the stored procedure with a C++ class (hereinafter referred to as xe2x80x9cdispatcherxe2x80x9d) 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. In one or more embodiments of the invention, a C++ class such as the TOR InputStream class available from NCR Corporation, the assignee of the present invention is utilized.