# US 10,503,734 B2 · External offline query optimization > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 10,503,734 B2 - Document ID: US10503734B2 - Status: Granted - Kind: B2 - Application number: 14/798,785 - Filed: 2015-07-14 - Published: 2017-01-19 - Granted: 2019-12-10 - Earliest claimed: 2015-07-14 - Assignees: Sap SE - Applicants: Sap SE - Inventors: Daniel Scheibli; Frank O. Schulz - Web page: https://patentgrep.com/patent/US10503734B2 ## CPC - G06F 16/24542: {Plan optimisation} - G06F 12/0802: Addressing of a memory level in which the access to the desired data or data block requires associative addressing means, e.g. caches - G06F 16/2453: Query optimisation - G06F 16/24539: {using cached or materialised query results} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract A determination is made that an execution plan for a received structure query language (SQL) database query to execute on a database is not present in a database plan cache. An external offline optimization process is triggered to generate an externally optimized execution plan for the SQL database query. The generation of the externally optimized execution plan includes: receiving all or part of the received SQL database query as SQL statements and metadata associated with the database, looping over and analyzing all the received SQL statements, selecting at least one an optimization strategy for the SQL statements; and executing the selected at least one optimization strategy to generate the externally optimized execution plan. The externally optimized execution plan is then pinned into the database plan cache. ## Claims ### Claim 1 A computer-implemented method, comprising: determining that an execution plan for a received structure query language (SQL) database query to execute on a database is not present in a database plan cache; triggering an external offline optimization process not part of a query execution process to generate a plurality of externally optimized execution plans for the SQL database query using an external SQL database query optimizer in an external, 2nd-stage, offline optimization environment not constrained by computing resources consumed by an ad-hoc query optimizer, wherein the ad-hoc query optimizer generates a default execution plan for the received database query in parallel with the generation of the plurality of externally optimized execution plans using the external offline optimization process, and wherein the generation includes: receiving all or part of the received SQL database query as SQL statements and metadata associated with the database; looping over and analyzing all the received SQL statements; selecting at least one optimization strategy for the SQL statements; and executing the selected at least one optimization strategy to generate the plurality of externally optimized execution plans; executing the plurality of externally optimized execution plans on the database to determine a best performing execution plan based on the lowest actual execution time on the database; and pinning the best performing execution plan into the database plan cache using the external, 2nd-stage, offline optimization environment and a direct interface exposed by the database plan cache. ### Claim 2 (depends on 1) The method of claim 1, wherein the triggering is a result of meeting or exceeding a threshold value associated with the received database query. ### Claim 3 (depends on 1) The method of claim 1, comprising replacing the default execution plan with the externally optimized execution plan. ### Claim 4 A non-transitory, computer-readable medium storing computer-readable instructions, the instructions executable by a computer and configured to: determine that an execution plan for a received structure query language (SQL) database query to execute on a database is not present in a database plan cache; trigger an external offline optimization process not part of a query execution process to generate a plurality of externally optimized execution plans for the SQL database query using an external SQL database query optimizer in an external, 2nd-stage, offline optimization environment not constrained by computing resources consumed by an ad-hoc query optimizer, wherein the ad-hoc query optimizer generates a default execution plan for the received database query in parallel with the generation of the plurality of externally optimized execution plans using the external offline optimization process, and wherein the generation includes instructions to: receive all or part of the received SQL database query as SQL statements and metadata associated with the database; loop over and analyzing all the received SQL statements; select at least one optimization strategy for the SQL statements; and execute the selected at least one optimization strategy to generate the plurality of externally optimized execution plans; execute the plurality of externally optimized execution plans on the database to determine a best performing execution plan based on the lowest actual execution time on the database; and pin the best performing execution plan into the database plan cache using the external optimization environment and a direct interface exposed by the database plan cache. ### Claim 5 (depends on 4) The non-transitory, computer-readable medium of claim 4, wherein the triggering is a result of meeting or exceeding a threshold value associated with the received database query. ### Claim 6 (depends on 4) The non-transitory, computer-readable medium of claim 4, comprising instructions to replace the default execution plan with the externally optimized execution plan. ### Claim 7 A system, comprising: a memory; at least one hardware processor interoperably coupled with the memory and configured to: determine that an execution plan for a received structure query language (SQL) database query to execute on a database is not present in a database plan cache; trigger an external offline optimization process not part of a query execution process to generate a plurality of externally optimized execution plans for the SQL database query using an external SQL database query optimizer in an external, 2nd-stage, offline optimization environment not constrained by computing resources consumed by an ad-hoc query optimizer, wherein the ad-hoc query optimizer generates a default execution plan for the received database query in parallel with the generation of the plurality of externally optimized execution plans using the external offline optimization process, and wherein the generation is configured to: receive all or part of the received SQL database query as SQL statements and metadata associated with the database; loop over and analyzing all the received SQL statements; select at least one optimization strategy for the SQL statements; and execute the selected at least one optimization strategy to generate the plurality of externally optimized execution plans; execute the plurality of externally optimized execution plans on the database to determine a best performing execution plan based on the lowest actual execution time on the database; and pin the best performing execution plan into the database plan cache using the external optimization environment and a direct interface exposed by the database plan cache. ### Claim 8 (depends on 7) The system of claim 7, wherein the triggering is a result of meeting or exceeding a threshold value associated with the received database query. ### Claim 9 (depends on 7) The system of claim 7, further configured to replace the default execution plan with the externally optimized execution plan.