Precompiled SQL Queries with Dynamic Column Selection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large-scale cloud-based multitenant computing systems face significant overheads and resource-intensive recompilation of SQL queries due to different execution plans being required for varying SQL statements, even if precompiled plans are cached, leading to performance degradation and increased resource usage.
Innovation Solution
Implementing a union all query statement that allows dynamic selection of columns using runtime variables, enabling a single query plan to be reused for multiple user queries accessing the same table, thereby reducing the need for repeated compilation and caching of query plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If different SQL statements are used for different queries, then query flexibility and adaptability are improved, but query compilation time and resource usage increase
Solution Approach 1:
The patent creates a universal query template that can serve multiple different query requests. By parameterizing the SQL statement with placeholders like :column1, :column2, and using a single compiled execution plan for various column combinations, the system achieves multi-functionality where one query structure handles many different query scenarios, eliminating repeated compilation while maintaining adaptability.
Solution Approach 2:
The patent performs preliminary compilation of a generic query template that includes all possible column selections. By pre-compiling the execution plan with parameter placeholders before actual queries are executed, the system prepares the query structure in advance, allowing subsequent queries to simply bind parameters rather than recompile, thus reducing compilation time for individual queries.
2Speed
If query plans are cached, then query execution speed is improved, but cache memory consumption increases
Solution Approach 1:
The patent stores a single universal execution plan in cache that can serve multiple different query requests with different column combinations. Instead of caching separate execution plans for each possible query variation, the universal plan with parameter placeholders occupies less cache space while still providing fast execution for multiple query types by binding different parameters at runtime.
3Adaptability or versatility
If query plans are frequently recompiled, then query adaptability is maintained, but system resource usage and processing time increase
Solution Approach 1:
The patent eliminates the need for frequent recompilation by creating a universal query template that covers multiple query scenarios. The single compiled execution plan with parameter placeholders can adapt to different column selection requirements without recompilation, as the adaptability is achieved through parameter binding rather than query rewriting, thus maintaining system throughput.
4Speed
If multiple query plans are cached for different SQL statements, then query performance is improved, but cache exhaustion occurs faster
Solution Approach 1:
The patent extends cache validity by storing a universal execution plan that serves multiple query patterns. Instead of having separate short-lived cache entries for each specific query variation, the universal plan remains valid and reusable for extended periods, as it can accommodate different parameter bindings without becoming stale, thus preventing cache exhaustion and extending cache lifetime.
Data Source
AI summary
A query to retrieve database values for a first subset of table columns in a database table is determined. Runtime column selection variables are set up to identify the first subset of table columns for data retrieval, and are dynamically bound to a prepared query statement with single-column query statements joined by interstitial union operators. Each single-column query statement in the prepared query statement specifies a single-column query directed to a corresponding table column with a corresponding predicate to be bound at runtime dynamically with a corresponding portion of the one or more runtime column selection variables. The predicate is evaluated to be true or false in dependence on whether the corresponding table column is in the first subset of table columns. The prepared query statement is executed to retrieve database values for the first subset of table columns in the database table.