Automated Query Optimization via ML and Shredded Explain Text

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Manual interactions between users and third parties for data conveyance are inefficient, slow, and insecure, and existing database query tuning methods require deep architectural knowledge and are not universally applicable, leading to suboptimal performance and resource issues in Teradata systems.

Innovation Solution

A system that analyzes and optimizes data queries using a combination of Teradata RDBMS, shell scripting, HADOOP, SPARK ML, and Python, which connects to a data management structure, shreds query plans, populates metadata tables, defines machine learning algorithms, and accesses historical logs to optimize queries, thereby improving performance without requiring deep system knowledge.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Manufacturing precision

If manual query tuning methods are used, then deep architectural knowledge can achieve optimization, but the process is time-consuming and requires expert intervention

Engineering Contradiction:

Improvequery optimization qualityVSAvoidquery tuning time

Core Design Contradiction:

Manufacturing precisionVSLoss of time

Solution Approach 1:

The system enables queries to optimize themselves automatically through machine learning algorithms that analyze query patterns, performance metrics, and system state without requiring manual expert intervention. The ML model continuously learns from historical data and autonomously generates optimization recommendations.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The patent replaces manual mechanical query tuning processes with automated machine learning systems. Instead of experts manually analyzing and tuning queries, an ML-based automated system performs the optimization, substituting human expert systems with algorithmic decision-making.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Reliability

If existing query tuning methods are used, then some performance issues can be resolved, but they are not universally applicable across different Teradata systems

Engineering Contradiction:

Improveperformance issue resolutionVSAvoiduniversal applicability

Core Design Contradiction:

ReliabilityVSAdaptability or versatility

Solution Approach 1:

The system creates a universal query optimization platform that adapts to different Teradata system configurations through machine learning. The ML model learns from diverse system configurations and query patterns, making it universally applicable across various Teradata environments rather than being tailored to specific systems.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The system dynamically adjusts optimization parameters based on system state, query characteristics, and historical performance data. The ML model modifies tuning parameters adaptively rather than using fixed rules, enabling universal applicability across different system configurations and workloads.

Inventive Principle:

Principle #35Parameter changes

3Reliability

If manual interactions are used for data conveyance, then security can be maintained through control, but efficiency and speed are reduced

Engineering Contradiction:

Improvedata securityVSAvoiddata conveyance efficiency

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system replaces manual data conveyance and query execution processes with automated machine learning-based systems. This substitution maintains security through controlled automated access while dramatically improving efficiency by eliminating manual intervention steps.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS10824624B2System for analyzing, optimizing, and remediating a proposed data query prior to query implementation

Publication Date: 2020.11.03 BANK OF AMERICA CORP

AI summary

Embodiments of the invention enable analyzing, optimizing and remediating a proposed data query prior to query implementation. Embodiments receive a request from a user; in response, perform an initialization comprising connecting to a data management structure; exporting an explain text of the request in a known format; and disconnecting from the data management structure. In response to initialization, embodiments perform a shredding step comprising shredding the explain text of the request; and populating a plurality of metadata tables comprising a superset table required for internal processing. Next, embodiments define or redefine a machine learning algorithm comprising a plurality of rulesets by calling a plurality of macros to act on the request; access a historic log comprising identified performance tuning parameters configured for tuning queries; and use the identified performance tuning parameters and the machine learning algorithm, optimize the query, thereby resulting in an optimized query.