SQL Sinking Statement Generation for Distributed Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Federated and distributed database systems face inefficiencies in processing speed and concurrency due to large network traffic and resource conflicts, particularly when optimizing Structured Query Language (SQL) operations across multiple data sources.
Innovation Solution
A database access method that employs a preset rule set for SQL transformation, generating optimized 'sinking' statements that maximize data source processing capabilities, reducing the need for cross-database processing and minimizing network traffic.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If SQL optimization is performed using conventional federated database methods, then query execution is attempted across distributed data sources, but this causes large network traffic and data re-hashing that reduces processing speed
Solution Approach 1:
The patent applies preliminary action by pre-compiling SQL statements into executable sinking statements before actual query execution. The SQL optimization compiler transforms standard SQL into optimized sinking statements that can be directly executed by data sources, avoiding the need for runtime SQL parsing and optimization across distributed systems. This pre-processing eliminates network traffic associated with SQL statement transmission and reduces data re-hashing operations.
Solution Approach 2:
The patent extracts the SQL optimization and transformation process from the runtime execution environment and moves it to a pre-compilation phase. By separating the SQL compilation step from the actual data query execution, the system eliminates network traffic generated during query optimization and prevents redundant data re-hashing operations that would otherwise occur when SQL statements are processed across multiple distributed data sources during runtime.
2Adaptability or versatility
If data is re-hashed or broadcast across the entire network for SQL optimization, then query results can be obtained from distributed sources, but this causes large occupation of processor and memory resources
Solution Approach 1:
The patent applies preliminary action by pre-compiling SQL statements into executable sinking statements before actual query execution. The SQL optimization compiler transforms standard SQL into optimized sinking statements that can be directly executed by data sources, avoiding the need for runtime SQL parsing and optimization across distributed systems. This pre-processing eliminates network traffic associated with SQL statement transmission and reduces data re-hashing operations.
Solution Approach 2:
The patent introduces an intermediary component - the SQL optimization compiler - that acts as a mediator between the user's SQL query and the distributed data sources. This compiler translates and optimizes SQL statements into sinking statements locally, serving as an intermediary that eliminates the need for complex runtime coordination and resource-intensive data re-hashing operations across the distributed network.
3Adaptability or versatility
If conventional SQL optimization is used in federated databases, then queries can be executed across multiple data sources, but this leads to reduction in concurrency processing performance and response delay
Solution Approach 1:
The patent applies preliminary action by pre-compiling SQL statements into executable sinking statements before actual query execution. The SQL optimization compiler transforms standard SQL into optimized sinking statements that can be directly executed by data sources, avoiding the need for runtime SQL parsing and optimization across distributed systems. This pre-processing eliminates network traffic associated with SQL statement transmission and reduces data re-hashing operations.
Solution Approach 2:
The patent enables continuous useful action by ensuring that pre-compiled sinking statements can be executed immediately without runtime compilation delays. The system maintains a ready-to-execute form of queries that can be continuously processed across multiple data sources without interruption for optimization, thereby maintaining high concurrency processing performance and minimizing response delays.
Data Source
Figure 1~2
Figure 3
Figure 4
AI summary
The present invention provides a database access method, where the method includes the following steps: receiving an access request input by a client, where the access request includes at least one command statement; performing rule matching processing between the command statement included in the access request and a rule in a preset rule set, and performing transformation processing on a statement that is included in the access request and that matches the rule, so as to generate a sinking statement that satisfies an execution requirement of a data source, where the sinking statement is a statement that is delivered to a database and executed by the data source; and transmitting the sinking statement to the database to which the sinking statement points. The present invention further provides a database access apparatus and a database system to which the foregoing method is applied.