Hashed SQL Wrapper for Precompiled Database Object Reuse
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for executing SQL statements in relational databases, such as precompiling or using libraries, face challenges in cloud environments due to the need for maintaining connections and creating execution plans, which cause delays and inefficiencies.
Innovation Solution
A wrapper functionality that generates a hash identification for SQL statements, checks for existing objects in a lookup table, and creates precompiled database objects if needed, allowing execution without recompiling, even when connections are closed.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If SQL statements are precompiled or libraries are used, then execution efficiency is improved, but connection maintenance and execution plan creation cause delays and reduce scalability in cloud environments
Solution Approach 1:
The patent applies preliminary action by precompiling SQL statements into database objects (such as stored procedures or views) before execution. The wrapper functionality generates a hash identification for each SQL statement, checks if a corresponding database object already exists, and creates it if needed. This allows the SQL statements to be executed without recompilation even when connections are closed, eliminating the time loss associated with connection maintenance while preserving execution efficiency.
2Reliability
If connections are maintained for SQL execution, then execution plans can be reused, but connection management complexity and resource consumption increase
Solution Approach 1:
The patent extracts the execution plan from the connection context by precompiling SQL statements into standalone database objects. The hash identification mechanism allows the system to retrieve and execute precompiled statements without maintaining active connections. This separates the execution plan reuse capability from connection management, reducing complexity while preserving reliability.
Solution Approach 2:
The patent creates copies of SQL statements in the form of database objects (stored procedures, views) that contain the compiled execution plans. These objects serve as persistent copies that can be executed without the original connection, eliminating the need for connection maintenance while preserving execution plan reuse.
3Adaptability or versatility
If SQL statements are compiled at runtime, then flexibility is maintained, but repeated compilation causes performance delays
Solution Approach 1:
The patent applies preliminary action by compiling SQL statements in advance and storing them as database objects with hash identifications. When the same SQL statement is encountered again, the system checks the hash and retrieves the precompiled object, avoiding repeated compilation while maintaining the ability to handle different SQL statements through the wrapper functionality.
Data Source
AI summary
In an example embodiment, rather than passing SQL statements directly from an application runtime to a relational database, the SQL statements are first passed to a wrapper functionality. The wrapper functionality takes the uncompelled SQL statements and arguments that need to be passed for the SQL statement as input parameters, executes the statement, and returns the results. This is accomplished by generating a hash identification out of the string SQL statement. The wrapper functionality then looks for a hash identification of an input SQL statement. If it exists, then a corresponding object in the database can be retrieved and executed. If the hash identification is not found, a database object is created that performs the same functionality as the SQL statement. At this time, the SQL statement is also precompiled, and an execution plan created either by the wrapper functionality or the relational database, or both).