Query Tuner Execution Plans Compilation Time Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Standard query optimizers often choose slower execution plans for complex queries due to inaccurate estimations, especially when using traditional statistics, which do not cover the full expressivity of query languages like SQL.

Innovation Solution

An enhanced query optimizer, referred to as a query tuner, performs actual execution of query fragments during compilation time to obtain actual results, combining them with statistical estimates to determine a more efficient execution plan, using top-down or bottom-up traversal methods with time and resource limits to control overhead.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If standard query optimizers use traditional statistics for estimation, then the query processing is fast, but the execution plan accuracy deteriorates for complex queries

Engineering Contradiction:

Improvequery processing speedVSAvoidexecution plan estimation accuracy

Core Design Contradiction:

ProductivityVSMeasurement precision

Solution Approach 1:

The system performs preliminary execution of query fragments during compilation time to obtain actual results before final plan selection. This preliminary action provides accurate data for complex queries without affecting runtime performance, resolving the contradiction between fast processing and accurate estimation.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system executes only certain query fragments (partial action) rather than complete queries, obtaining sufficient actual results for plan optimization without excessive resource consumption. This selective execution maintains productivity while improving estimation accuracy for complex queries.

Inventive Principle:

Principle #16Partial or excessive action

2Measurement precision

If more sophisticated statistics such as multi-dimensional histograms are used, then the estimation accuracy is improved, but the code complexity increases and full query language expressivity is not covered

Engineering Contradiction:

Improveestimation accuracyVSAvoidcode complexity

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The system creates execution copies of query fragments during compilation to obtain actual results. These copies provide accurate data without requiring complex statistical structures or code, achieving high estimation accuracy with minimal complexity increase.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The system replaces the mechanical statistical estimation system (histograms, multi-dimensional statistics) with an execution-based measurement system. By actually running query fragments and measuring results, the system achieves full query language expressivity coverage without code complexity.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

3Measurement precision

If actual execution of query fragments is performed during compilation time, then the execution plan accuracy is improved, but the compilation time and resource consumption increase

Engineering Contradiction:

Improveexecution plan accuracyVSAvoidcompilation time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The system executes only partial query fragments (not complete queries) during compilation, obtaining sufficient actual results for accurate plan selection without excessive time consumption. This partial execution resolves the contradiction between accuracy and compilation time.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The system uses actual execution results as feedback to refine and select the optimal execution plan. This feedback mechanism provides high accuracy while limiting resource consumption by using results only for plan selection, not for complete query execution.

Inventive Principle:

Principle #23Feedback

4Productivity

If a limit such as time limit or level limit is used to control resource consumption, then the productivity is maintained, but the measurement precision may be reduced for very complex queries

Engineering Contradiction:

Improveresource efficiencyVSAvoidexecution plan accuracy

Core Design Contradiction:

ProductivityVSMeasurement precision

Solution Approach 1:

The system performs partial execution of query fragments within defined limits, obtaining sufficient actual results for most queries while maintaining resource efficiency. For very complex queries, the partial results still provide useful guidance for plan selection, balancing productivity and precision.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The system performs preliminary limited execution to obtain initial actual results that guide subsequent plan selection. This preliminary action with limits maintains resource efficiency while providing enough accuracy for practical query optimization.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS10983998B2Query execution plans by compilation-time execution

Publication Date: 2021.04.20 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Described is a query optimizer comprising a query tuner that performs actual execution of query fragments to obtain actual results during compilation time, and uses those actual results to select a query plan. The actual results may be combined with estimates for fragments that were not executed. The tree may be traversed in a top-down traversal, processing every node. Alternatively, the tree may be traversed in a bottom-up traversal, re-deriving data for higher nodes as each lower level is completed. A limit, such as a time limit or level limit, may be used to control how much time is taken to determine the execution plan.