Merged SQL Statement for Query Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data virtualization systems suffer from poor statistical/report query performance due to inefficient handling of multiple database queries, leading to increased network transmission and repeated full table scans, which degrade overall query performance.
Innovation Solution
A method that optimizes statistical query performance by receiving a structured query language (SQL) set, identifying matching parameters, creating a merged SQL statement, binding parameters, generating a remote SQL statement, and executing a commit statement to reduce network transmissions and minimize table scans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If multiple SQL statements are pushed down to data sources without merging, then query simplicity is maintained, but network transmission increases and table scans repeat
Solution Approach 1:
The patent merges multiple SQL statements into a single merged SQL statement that queries multiple tables simultaneously. This combines separate network requests into one, reducing network transmission overhead while maintaining the ability to access data from multiple sources. The merged statement consolidates what would otherwise be multiple individual queries into a single efficient operation.
2Adaptability or versatility
If multiple SQL statements are executed separately, then query independence is maintained, but table scans repeat and performance degrades
Solution Approach 1:
The patent combines multiple independent SQL statements into a merged statement that executes once to retrieve data from multiple tables. This maintains query independence in terms of functionality while improving performance by eliminating repeated table scans. The merged statement preserves the ability to query different tables with different conditions while executing them in a single coordinated operation.
3Device complexity
If SQL statements are not merged, then implementation simplicity is maintained, but scanning of data sources increases
Solution Approach 1:
The patent merges multiple SQL statements into a single merged SQL statement that scans data sources once instead of multiple times. This reduces the total quantity of data scanning while maintaining implementation through a systematic merging process that combines statements with matching parameters. The approach reduces redundant scanning operations while preserving the ability to access data from multiple sources.
4Ease of operation
If serial query is used, then query simplicity is maintained, but network transmission is large and performance degrades
Solution Approach 1:
The patent merges multiple serial queries into a single merged SQL statement that executes in parallel across multiple tables. This maintains query simplicity in terms of usage while dramatically improving performance by reducing the number of sequential network round trips. The merged statement allows the database to process multiple table queries simultaneously, reducing total execution time.
Data Source
AI summary
An approach for optimizing statistical query performance. The approach receives a structured query language set. The approach identifies a first set of parameters associated with the statements of the SQL set. The approach creates a merged SQL statement based on one or more matching parameters of SQL statements in the SQL set. The approach binds a second set of parameters associated with the merged SQL statement to the merged SQL statement. The approach generates a SQL statement based on the merged SQL statement. The approach generates a remote SQL statement based on the SQL statement. The approach executes a commit statement on the remote SQL statement.