Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-06-28
2002-03-12
Mizrahi, Diane D. (Department: 2171)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06356887
ABSTRACT:
FIELD OF THE INVENTION
This invention relates generally to database systems, and more particularly to auto-parameterizing queries that operate against a database.
COPYRIGHT NOTICE/PERMISSION
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever. The following notice applies to the software and data as described below and in the drawings hereto: Copyright © 1998, Microsoft Corporation, All Rights Reserved.
BACKGROUND OF THE INVENTION
SQL query compilation in database systems can take significant time when compared to the execution time of some simple queries. For example, the execution time to lookup a row in an index and modify some of its fields can be considerably smaller than the time it takes to parse, normalize, optimize, and prepare the corresponding SQL statement into the particular set of sub-queries that perform the required operations with the least processing cost (commonly referred to as an “execution plan”). When many simple requests are issued to a database, compilation time consumes a major portion of system resources.
A common approach to address this problem has been through the use of stored procedures. A stored procedure contains one of more SQL statements that are compiled once into their respective execution plans and stored in main memory, so they can be executed multiple times without having to be recompiled. Parameters in the stored procedure provide flexibility to this approach. For example, a stored procedure can do the database work needed to transfer money between two accounts. Such a procedure requires touching a few rows, perhaps in different tables, and it is parameterized off account numbers and amount. After the stored procedure is compiled and stored in memory, there is no need to re-compile it for later executions. Stored procedures are a standard feature in current database systems.
When applications use dynamic SQL, as opposed to stored procedures, database systems typically need to compile each statement, then execute. Many applications currently use dynamic SQL, as opposed to stored procedures, for several reasons. Some of them are legacy code; others did not want to manage the overhead of stored procedures, which are persistent objects and as such require some amount of administration. When those applications issue a large number of simple statements, compilation time can become the dominant cost factor.
Therefore, what is needed is a mechanism to detect similar queries that are issued from a number of database users so as to avoid the multiple re-compilation of such queries while not incurring the overhead of stored procedures.
SUMMARY OF THE INVENTION
The above-mentioned shortcomings, disadvantages and problems are addressed by the present invention, which will be understood by reading and studying the following specification.
An auto-parameterization process transforms a database query that is input to a database server into a parameterized basic query form by replacing any constant values in the query with parameters. The auto-parameterization process attempts to generate a safe execution plan from the basic query form if there is currently no such plan available. A safe execution plan is defined as an execution plan that is optimal over a range of values for the parameters. If a safe execution plan can be generated, it is passed to the execution stage of the database server, along with the constant values that were present in the query. If a safe execution plan cannot be generated, the auto-parameterization process passes an execution plan that contains the specific parameters to the execution stage. The safe execution plan is cached either at the time it is created or at the time it is executed. The cache is searched each time a parameterized basic query plan is generated by the auto-parameterization process.
In one aspect of the invention, the auto-parameterization process analyzes the query before creating the corresponding parameterized basic query form to determine if it is likely that a safe execution plan can be generated for the query.
The present invention describes systems, clients, servers, methods, and computer-readable media of varying scope. In addition to the aspects and advantages of the present invention described in this summary, further aspects and advantages of the invention will become apparent by reference to the drawings and by reading the detailed description that follows.
REFERENCES:
patent: 5822750 (1998-10-01), Jou et al.
patent: 5950186 (1999-09-01), Chaudhuri et al.
patent: 6032143 (2000-02-01), Leung et al.
patent: 6067542 (2000-05-01), Carino, Jr.
Lee, et al. (IEEE publication) discloses optimizing large join queries using a graph-based approach; Dept. of Computer Science. & Inf. Eng., Taiwan, pp. 298-315, vol. 13, (2), 4-2001.*
An, N. et al. (IEEE publication) discloses selectivity estimation for spatial joins; Dept. of Computer Science, Penn. State Univ., (NSF Grant R825195-01-0); pp. 368-375, (2001).
Berenson Harold R.
Carlin Peter A.
Ellis Nigel R.
Galindo-Legaria Cesar A.
Graefe Goetz
Microsoft Corporation
Mizrahi Diane D.
Woodcock & Washburn LLP
LandOfFree
Auto-parameterization of database queries does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Auto-parameterization of database queries, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Auto-parameterization of database queries will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2818145