External offline query optimization

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.

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.