Relational database scalar subquery optimization
AssigneeMicrosoft Corporation
InventorsTorsten Grabs, Adrian Baras, Cesar Galindo-Legaria
Life of this patent
Estimating the term…
Abstract
When two or more scalar subtrees in an execution plan are directed towards the same data set, but request different data from the data set, the two subtrees are replaced in the execution plan with a new subtree which queries the data set only once. This new query to the data set covers all data required by the different subtrees. A pivoting step then is used to separate the data which would have been sought by each subtree. The pivoting step also yields data in the same form as would have resulted from the original subtrees.
Claims20
A method for optimizing a query plan comprising:
- identifying at least two or more subqueries of said query plan, where each of said subqueries is directed towards the same data set, where a first subquery from among said subqueries requests first data from among said data set, and a second subquery from among said subqueries requests second data from said data set;
- replacing said subqueries in said query plan with a new subquery, where said new subquery queries said data set for both said first data and said second data.
A computer-readable medium comprising computer-executable instructions for retrieving data from a database, said computer-executable instructions for performing steps comprising:
- accepting a query;
- creating a query plan from said query;
- identifying at least two or more subqueries of said query plan, where each of said subqueries is directed towards the same data set in said database, where a first subquery from among said subqueries requests a first property from said data set, and a second subquery from among said subqueries requests a second property from said data set;
- replacing said subqueries in said query plan with a new subquery, where said new subquery queries said data set for both said first property and said second property.
A system for optimizing a query plan comprising:
- a related subquery identifier for identifying at least two or more subqueries of said query plan, where each of said subqueries is directed towards the same data set, where a first subquery from among said subqueries requests first data from among said data set, and a second subquery from among said subqueries requests second data from said data set;
- an equivalent subquery producer for producing a new subquery, where said new subquery queries said data set for both said first data and said second data;
- and
- a subquery replacer for producing a new query plan by replacing said two or more subqueries of said query plan with said new subquery.
Dependencies are inferred from the claim text.