US 12,393,582 B2
Automatic recompilation for parameterized queries
Dalsu Choi, Seoul (KR); Sanghee Lee, Seoul (KR); and Mijin An, Seoul (KR)
Assigned to SAP SE, Walldorf (DE)
Filed by SAP SE, Walldorf (DE)
Filed on Jan. 31, 2024, as Appl. No. 18/428,595.
Prior Publication US 2025/0245225 A1, Jul. 31, 2025
Int. Cl. G06F 16/2453 (2019.01)
CPC G06F 16/24542 (2019.01) [G06F 16/24539 (2019.01); G06F 16/24549 (2019.01)] 18 Claims
OG exemplary drawing
 
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.