Database Query Optimization via Placeholder Substitution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Users of pre-developed database management systems from independent software vendors face challenges in optimizing query performance due to uneditable object code formats, which restrict their ability to replace problematic SQL statements, leading to inefficient query execution.
Innovation Solution
A computer program product that receives a database query, replaces literal terms with placeholder terms, searches a directory for a corresponding optimized query, and replaces placeholders with literal terms to generate an optimized executable query, thereby enhancing query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If ISV DB programs are provided in uneditable object code format for multiple usage on various platforms, then adaptability and ease of operation are improved, but the ability to optimize query performance deteriorates
Solution Approach 1:
The patent introduces an intermediary system consisting of a query rewrite engine and a rules database that mediates between the uneditable ISV DB program and the optimization needs. The rewrite engine intercepts queries, applies optimization rules from the database, and returns optimized versions without requiring modification of the original ISV program code.
Solution Approach 2:
The system creates optimized copies of queries by generating alternative SQL statements that achieve the same results more efficiently. The query rewrite engine produces rewritten query versions that substitute suboptimal SQL constructs with optimized equivalents while maintaining the same functional output.
2Ease of operation
If ISV DB programs use generic SQL statements for multiple platforms, then ease of operation is improved, but manufacturing precision deteriorates
Solution Approach 1:
The system applies local quality by allowing different optimization rules to be applied to different parts of the database system. Specific rules can be tailored for different database tables, query types, or performance criteria, enabling precise optimization in critical areas while maintaining generic operation elsewhere.
Solution Approach 2:
The query rewrite engine changes parameters of SQL statements by modifying query structures, joining strategies, indexing approaches, and execution plans. These parameter changes transform generic SQL statements into optimized versions that are precisely tuned for specific database characteristics and workloads.
3Adaptability or versatility
If users rely on speculative process to persuade ISV to modify DB programs, then adaptability is maintained, but loss of time increases
Solution Approach 1:
The system performs preliminary action by pre-compiling and storing optimization rules in a rules database before they are needed. The query rewrite engine has immediate access to these pre-prepared optimization strategies, eliminating the need for time-consuming speculative discussions with ISVs and enabling instant query optimization.
Solution Approach 2:
The system enables self-service by allowing database administrators to independently configure and apply optimization rules without requiring vendor intervention. The automated rewrite engine provides immediate optimization capabilities, eliminating dependency on external ISV support and reducing time losses associated with vendor communication cycles.
Data Source
AI summary
A computer program product for database query optimization including a computer usable medium having a computer readable program, wherein the computer readable program, when executed on a computer, causes the computer to receive a database query, search the database query for a literal term, replace the literal term in the database query with a placeholder term to put the database query in a template form, search a directory for a replacement query that corresponds to the database query in the template form, replace the database query with the replacement query and include the placeholder term in the replacement query, and replace the placeholder term in the replacement query with the corresponding literal term to put the replacement query in an executable format, wherein the replacement query is an optimized substitute for the database query.