PROGRESSIVE PARAMETRIC QUERY OPTIMIZATION

• US 20080172356A1
• Filed: 01/17/2007
• Published: 07/17/2008
• Est. Priority Date: 01/17/2007
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Bruno, Nicolas, Bizarro, Pedro Gustavo Santos Rodrigues
• Application Number:
  US11/623,957
• Publication Number:
  US 20080172356A1
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A system and method that facilitates and effectuates database query optimization, comprising a progressive optimization component that receives a query and associated parametric values, and based at least in part on the received query, the progressive optimization component determines whether or not the query has been submitted in the past. Where the query has been previously submitted, the progressive optimization component locates an optimal or near-optimal execution plan based on previously persisted and similarly situated and optimized execution plans that can subsequently be used to evaluate the query and its associated parametric values thereby obviating unnecessary and repeated calls to a database optimizer for queries that are similar though not necessarily identical.

Claims

1. A machine implemented system that effectuates and facilitates database query optimization, comprising:

a progressive optimization component that receives a query and associated parametric values, based at least in part on the query the progressive optimization component determines whether the query has been previously received, and based at least on the determination the progressive optimization component generates from previously received queries an optimal or near-optimal execution plan utilized to evaluate the query and associated parametric values.

2. The system of claim 1, based at least in part on the determination the progressive optimization component conveys the query to a database optimizer.

3. The system of claim 2, the database optimizer, based at least on the query generates the optimal execution plan and associates an estimated execution cost with the optimal or near-optimal execution plan.

4. The system of claim 1, the previously received queries persisted in a data store associated with the progressive optimization component.

5. The system of claim 4, the progressive optimization component consults the data store to determine whether the query has been previously received.

6. The system of claim 1, the progressive optimization component intercepts the query and associated parametric values before the query and associated parametric values can be conveyed to a database optimizer.

7. The system of claim 6, the progressive optimization component intercepts and persists an output from the database optimizer.

8. The system of claim 7, the output includes one of the optimal or near-optimal execution plan or an estimated cost associated with executing the optimal execution plan.

9. The system of claim 1, the progressive optimization component employs a technique that generates the optimal or near-optimal execution plan based on a bounded cost specified by a user.

10. The system of claim 1, the progressive optimization component utilizes a technique that minimizes the number of optimization calls made to a database optimizer.

11. A method implemented on a machine that effectuates database query optimization, comprising:

receiving a query and associated values;

determining whether the query has previously been received;

based at least in part on the query, obtaining an optimal execution plan and an expected cost associated with execution of the optimal execution plan; and

utilizing the optimal execution plan to evaluate the query and associated values.

12. The method of claim 11, based at least in part on the obtaining, persisting at least one of the query, the optimal execution plan, or the expected cost associated with execution of the optimal execution plan to preclude future obtaining of the optimal execution plan for a similar query and similar accompanying values.

13. The method of claim 11, based at least in part on the determining, transmitting the query to an optimizer associated with a database management system.

14. The method of claim 13, the optimizer, based on at least on the query, providing the optimal execution plan and the associated expected cost to execute the query and associated values.

15. The method of claim 11, further comprising utilizing a technique that generates the optimal execution plan based at least in part on a bounded cost specified by a user.

16. The method of claim 11, further comprising employing a modality that minimizes optimization calls to an optimizer associated with a database management system.

17. A system that effectuates database query optimization, comprising:

means for accepting one or more parametric queries;

means for determining whether the one or more parametric queries have previously been accepted; and

means for locating an optimal execution plan based on the results from the means for determining.

18. The system of claim 17, the means for locating includes a means for persisting that stores the optimal execution plan based on the one or more parametric queries and an estimated cost associated with executing the optimal execution plan for the one or more parametric queries.

19. The system of claim 17, the means for locating includes employing a means for optimizing that returns at least the optimal execution plan and an estimated cost associated with executing the optimal execution plan for the one or more parametric queries.

20. The system of claim 19, the means for locating, based at least in part on the results from the means for determining, obviates utilization of the means for optimizing when the one or more parametric queries have previously been accepted.