News Column

Patent Issued for Query Optimization with Awareness of Limited Resource Usage

May 13, 2014

By a News Reporter-Staff News Editor at Information Technology Newsweekly -- From Alexandria, Virginia, VerticalNews journalists report that a patent by the inventors Mandal, Prateem (Magarpatta, IN); Seputis, Edwin Anthony (Oakland, CA), filed on September 22, 2009, was published online on April 29, 2014.

The patent's assignee for patent number 8712972 is Sybase, Inc. (Dublin, CA).

News editors obtained the following quote from the background information supplied by the inventors: "The present invention is directed to tuning query execution performance of a database system through query optimization with awareness of temporary database usage.

"Computers are very powerful tools for storing and providing access to vast amounts of information. Databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical relational database is an organized collection of related information stored as 'records' having 'fields' of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.

"Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about the underlying hardware-level details.

"One purpose of a database system is to answer queries requesting information from the database. A query may be defined as a logical expression over the data and the data relationships set forth in the database, and execution of a query results in the identification of a subset of the database. In operation, for instance, the execution of a request for information from a relational DBMS is typically issued by a client system as one or more Structured Query Language or 'SQL' queries for retrieving particular data (e.g., a list of all employees earning more than $25,000) from database tables on a server. In response to this request, the database system typically returns the names of those employees earning $25,000, where 'employees' is a table defined to include information about employees of a particular organization. The syntax of SQL is well documented, see e.g., 'Information Technology--Database languages--SQL', published by the American National Standards Institute as American National Standard ANSI/ISO/IEC 9075: 1992, the disclosure of which is hereby incorporated by reference.

"SQL queries express what results are requested but do not state how the results should be obtained. In other words, the query itself does not tell how the query should be evaluated by the DBMS. Rather, a component of the DBMS called the optimizer determines the 'plan' or the best method of accessing the data to implement the SQL query. The query optimizer is responsible for transforming a SQL request into an access plan composed of specific implementations of the algebraic operator selection, projection, join, and so forth. The role of a query optimizer in a relational DBMS system is to find an adequate execution plan from a search space of many semantically equivalent alternatives. An example of a learning optimizer is described in U.S. Pat. No. 6,763,359, which provides an approach for selection of a query execution plan that utilizes statistics collected during query execution that are fed back to an optimizer to allow it to learn of a modeling mistake in a query execution plan.

"During typical query optimization, the optimizer chooses a plan which is estimated to be least costly. A so-called cheapest plan that the optimizer selects may fail to produce the desired result when the resources needed to run the plan are not available, e.g., when overuse of a limited resource causes starvation of that resource for other query plans. Temporary database (tempdb) storage is one type of limited resource.

"In general, the tempdb storage provides a temporary workspace that is relied upon by database applications for storing intermediate results and data that does not need to be persistent. In determining least cost, generally, an optimizer assumes that there is an unlimited amount of tempdb and tries to come up with a plan that is cheapest within that scenario. However, when the system doesn't have the required amount of tempdb needed for a plan, the optimized final plan may not be executable in the system. While the aforementioned U.S. Pat. No. 6,763,359 generally points out that configuration parameters, such as buffer pool size, can be made self-tuning in response to a detected lack of a resource for certain queries, it fails to provide a manner of determining the necessary information to perform such tuning nor does it provide an approach for evaluating and utilizing limited resource usage data, including tempdb usage, that would allow awareness and consideration by the optimizer during plan selection.

"Accordingly, a need exists for an ability to provide awareness of limited resource usage, including tempdb usage, for query optimization. The present invention provides a solution for this and other needs."

As a supplement to the background information on this patent, VerticalNews correspondents also obtained the inventors' summary information for this patent: "Briefly stated, the invention includes system, method, computer program product embodiments and combinations and sub-combinations thereof for tuning query execution performance in a database management system. In an aspect, query optimization in a database management system (DBMS) with awareness of limited resource usage includes identifying limited resource usage for each possible plan being considered for query execution by a query engine. A determination of whether any possible plan has limited resource usage that does not exceed a threshold level is made, and when at least one possible plan does not exceed the threshold level, the identified limited resource usage is included as a consideration during plan selection by the query engine. In an embodiment, temporary database usage is identified for each possible plan.

"Further embodiments, features, and advantages of the invention, as well as the structure and operation of the various embodiments of the invention, are described in detail below with reference to accompanying drawings."

For additional information on this patent, see: Mandal, Prateem; Seputis, Edwin Anthony. Query Optimization with Awareness of Limited Resource Usage. U.S. Patent Number 8712972, filed September 22, 2009, and published online on April 29, 2014. Patent URL:

Keywords for this news article include: Sybase Inc, Information Technology, Information and Data Management.

Our reports deliver fact-based news of research and discoveries from around the world. Copyright 2014, NewsRx LLC

For more stories covering the world of technology, please see HispanicBusiness' Tech Channel

Source: Information Technology Newsweekly

Story Tools Facebook Linkedin Twitter RSS Feed Email Alerts & Newsletters