Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1998-12-16
2002-08-13
Jurg, David (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C345S215000
Reexamination Certificate
active
06434545
ABSTRACT:
TECHNICAL FIELD
The present invention relates generally to the field of query processors conventionally used to manipulate and retrieve data from databases. More particularly, the present invention relates to a system and method for displaying an intuitive, graphical representation of query execution, including detailed computational cost statistics.
BACKGROUND OF THE INVENTION
The databases which enterprises use to maintain information naturally grow over time. Thus, database manipulation often requires operations involving large scale computing costs and undesirable delays.
As a result, the efficiency of database applications is of particular interest to database administrators and application developers. At the same time, the computer languages most often used for such applications, particularly in the relational database context, do not especially lend themselves to efficiency improvements. In particular, Structured Query Language (SQL) and similar query languages consist of high level commands, often referred to as “queries,” which typically describe a data set to be retrieved, selected or otherwise processed. While such programming environments allow the specification of data selection using constructs similar to natural English, they do not allow developers to specify the actual procedure used to accomplish the data selection. Thus, performance (i.e. efficiency) enhancement via DML statement revision alone is generally difficult to achieve.
In an effort to address these problems, some DBMS include graphical “show plans”. These allow a developer to specify a query and, without actually executing the query, view graphically the actual steps which would have been performed in achieving the specified data selection had the query been executed. More particularly, these show plans provide a means for visualizing a query's physical execution strategy. These plans may also provide the query's “cost” (i.e., an indication of the computational resources which would be consumed by the DBMS during query execution). The developer can then use this information to determine whether to actually implement the query analyzed.
Unfortunately, aside from a general indication of query cost, such “show plans” provide little in the way of the computational cost statistics. This lack of detail significantly limits the usefulness of such show plans in improving application performance. In particular, performance problems tend to arise from very specific causes (e.g., querying an insufficiently maintained or improperly designed database table). Thus, a general indication of the cost of queries, which typically hide numerous operations (e.g., scan, sort) involving multiple tables, is unlikely to indicate the source of a performance problem, or suggest how a query, application or database should be revised.
In short, what is needed is query analyzer providing detailed query cost information, including information specific to particular query operations, all in graphical representation designed to convey intuitively the source of performance problems.
SUMMARY OF THE INVENTION
The present invention addresses these needs. It allows a user to specify a batch of queries and view a tree-structure representation of the queries' execution plan (i.e. operations which will be executed). The graphical display of the present invention is designed to pictorially convey a maximum of information relating to query execution. In particular, each query is represented by a tree and each query operation belonging to a query is represented by a corresponding tree node.
Tree nodes are displayed to convey execution ordering intuitively so that, for example, a parent node will typically correspond to an operation calling another operation (i.e. represented as the child). In addition, because all defined query operations are preferably assigned a corresponding unique icon symbol, the operations which will be executed can be determine from the display alone.
To further improve the usefulness of the display as an analysis tool, the display shows the computational cost of each operation (i.e., at each node) as a percentage of overall query cost, and the cost of each query as a percentage of the overall cost of the specified batch. Thus, a developer can almost immediately narrow the search for the cause of performance problems. With a view to conveying still more information pictorially, the graphical analyzer of the present invention may display tree branches (i.e., an operation's connection to a parent) with widths bearing a mathematical relation (e.g., logarithmic) to the operation's cost.
If while using the display the user identifies a promising area for further investigation (e.g. a particular query operation), the user may select the operation (e.g., via a conventional “mouse” pointer), whereupon the user interface will show more detailed cost statistics relating to the selected operation. These may include, for example, the estimated number of database rows output by the operator, as well as estimated Input/Output (I/O) and CPU activity costs etc.
Providing still further advantages, the graphical display of the present invention may be enabled during normal application execution. By choosing this option, a developer is more likely to trouble-shoot the cause of a problem which manifests itself during such normal application use.
Moreover, the present invention may also incorporate one or more processes which detect missing indices and statistics relating to tables involved in the displayed execution plan. Database table indices and statistics are described in the detailed description below. Briefly however, database statistics comprise information stored in a database relating to a table's characteristics (e.g., number of rows). Indices are associative structures which, for example, facilitate rapid seek operations in relational database tables. In both cases, the query processor may utilize such enhanced information to formulate an execution plan characterized by improved performance.
Upon detection of missing indices or statistics, the query analyzer of the present invention may prompt a user to add such information. Thus, even where the developer fails to locate the source of a performance problem, the query analyzer of the present invention may facilitate semi-automatic enhancement of query performance.
Further benefits and advantages of the present invention will be made apparent in the more detailed description below.
REFERENCES:
patent: 5418944 (1995-05-01), DiPace et al.
patent: 5812840 (1998-09-01), Shwartz
patent: 5953723 (1999-09-01), Linoff et al.
www.dbmsmag.com/9701d161.html, as cached in www.google.com.*
www.cai.com/products/db2/plananalyzer/factsheet.htm.
Kiernan Casey L.
MacLeod Stewart P.
Vasandani Manu
Jurg David
Microsoft Corporation
Woodcock & Washburn LLP
LandOfFree
Graphical query analyzer does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Graphical query analyzer, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Graphical query analyzer will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2955278