Automatic recompilation for parameterized queries

Patent metadata

Field	Value
Publication number	US-12393582-B2
Application number	US-202418428595-A
Country	US
Kind code	B2
Filing date	Jan 31, 2024
Priority date	Jan 31, 2024
Publication date	Aug 19, 2025
Grant date	Aug 19, 2025

Abstract

A computer-implemented method can receive a parameterized query with an input parameter set, wherein the parameterized query has a query plan stored in a plan cache, determine an estimated compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set based on a compilation history associated with the parameterized query, determine an estimated execution time for executing the parameterized query with the input parameter set by using the query plan based on an execution history associated with the query plan, and determine a cache gain based at least in part on the estimated compilation-plus-execution time and the estimated execution time. Responsive to finding that the cache gain is positive, the method can execute the parameterized query with the input parameter set by using the query plan. Otherwise, the method can compile and execute the parameterized query with the input parameter set.

First claim

What is claimed is:

1. A computer-implemented method for optimizing runtime processing of a parameterized query by automatically selecting between reusing or recompiling a query plan based on dynamic execution conditions, the method comprising:
receiving a parameterized query with an input parameter set for the parameterized query, wherein the parameterized query has a query plan stored in a plan cache, wherein the parameterized query is associated with a compilation history comprising compilation-plus-execution times for compiling and executing the parameterized query with a first plurality of unique parameter sets, wherein the query plan is associated with an execution history comprising execution times for executing the parameterized query with a second plurality of unique parameter sets by using the query plan;
determining an estimated compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set, comprising: determining, in runtime, one unique parameter set among the first plurality of unique parameter sets stored in the compilation history that is most similar to the input parameter set;
determining an estimated execution time for executing the parameterized query with the input parameter set by using the query plan, comprising: determining, in runtime, one unique parameter set among the second plurality of unique parameter sets stored in the execution history that is most similar to the input parameter set;
determining, in runtime, a cache gain based at least in part on the estimated compilation-plus-execution time and the estimated execution time;
responsive to finding that the cache gain is positive, executing the parameterized query with the input parameter set by using the query plan; and
responsive to finding the cache gain is not positive, compiling and executing the parameterized query with the input parameter set.

2. The method of claim 1 , further comprising:
increasing a first counter responsive to finding that the cache gain is positive;
increasing a second counter responsive to finding that the cache gain is not positive;
determining a number of executions of the parameterized query since last evaluation of whether the query plan in the plan cache needs to be updated;
responsive to finding that the number of executions of the parameterized query since last evaluation reaches a predetermined number, determining, in runtime, a ratio of the first counter to the second counter;
responsive to finding that the ratio of the first counter to the second counter is below a predetermined threshold, updating, in runtime, the query plan in the plan cache with a new query plan generated by compilation of the parameterized query with the input parameter set.

3. The method of claim 2 , further comprising updating, in runtime, the compilation history associated with the parameterized query after updating the query plan in the plan cache, wherein the updating the compilation history comprises:
determining a new compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set;
determining whether the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history;
responsive to finding that the input parameter set is one of the first plurality of unique parameter sets stored in the compilation history, updating, in runtime, the compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set in the compilation history based at least in part on the new compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set; and
responsive to finding that the input parameter set is not one of the first plurality of unique parameter sets stored in the compilation history, inserting, in runtime, the new compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set into the compilation history.

4. The method of claim 3 , wherein the inserting the new compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set into the compilation history comprises:
determining a count of the first plurality of unique parameter sets stored in the compilation history; and
responsive to finding that the count of the first plurality of unique parameter sets stored in the compilation history is equal to a predefined size of the compilation history, removing, in runtime, the compilation-plus-execution time for compiling and executing the parameterized query with one of the first plurality of unique parameter sets from the compilation history.

5. The method of claim 3 , further comprising updating, in runtime, the execution history associated with the query plan after updating the compilation history associated with the parameterized query, wherein the updating the execution history comprises, in an iterative operation:
executing the parameterized query with a new parameter set for the parameterized query using the updated query plan in the plan cache;
determining a new execution time for executing the parameterized query with the new parameter set for the parameterized query;
determining whether the new parameter set is one of the second plurality of unique parameter sets stored in the execution history;
responsive to finding that the new parameter set is one of the second plurality of unique parameter sets stored in the execution history, updating, in runtime, the execution time for executing the parameterized query with the new parameter set in the execution history based at least in part on the new execution time for executing the parameterized query with the new parameter set; and
responsive to finding that the new parameter set is not one of the second plurality of unique parameter sets stored in the execution history, inserting, in runtime, the new execution time for executing the parameterized query with the input parameter set into the execution history,
wherein the iterative operation continues until a count of the second plurality of unique parameter sets stored in the execution history is equal to a predefined size of the execution history.

6. The method of claim 1 , wherein the determining one unique parameter set among the first or second plurality of unique parameter sets that is most similar to the input parameter set comprises:
measuring, in runtime, distances between the input parameter set and the first or second plurality of unique parameter sets; and
identifying, in runtime, a smallest distance among the measured distances.

7. The method of claim 1 , wherein the determining one unique parameter set among the first or second plurality of unique parameter sets that is most similar to the input parameter set comprises:
determining, in runtime, a selectivity value of the input parameter set and selectivity values of the first or second plurality of unique parameter sets; and
identifying, in runtime, a selectivity value of one of the first or second plurality of unique parameter sets that is closest to the selectivity value of the input parameter set.

8. The method of claim 1 , wherein the query plan is one of multiple query plans for the parameterized query stored in the plan cache, wherein the execution history is one of multiple execution histories respectively associated with the multiple query plans, wherein the estimated execution time is one of multiple estimated execution times respectively determined for the multiple query plans, wherein the cache gain is determined based on comparing the estimated compilation-plus-execution time with the multiple estimated execution times.

9. The met

Assignees

• Sap Se

Inventors

• Choi Dalsu
• Lee Sanghee
• An Mijin

Classifications

• G06F16/2433

  Query languages · CPC title

• G06F16/2453

  Query optimisation · CPC title

• G06F16/2455

  Query execution · CPC title

• G06F8/41 Primary

  Compilation · CPC title

• G06F16/25

  Integrating or interfacing systems involving database management systems · CPC title