SQL Query Optimization Using Representative Small Data Sampling

Solution Overview

Problem

Existing query optimization methods for big data environments are inefficient and time-consuming, particularly when dealing with low-performance SQL queries, as they require granular steps that consume significant resources and time, leading to degraded system performance.

Innovation Solution

A computer-implemented method that generates a 'small data' environment from the big data environment by identifying relevant tables and fields, sampling records with common data traits, and optimizing queries within this smaller dataset to improve performance.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If traditional query optimization methods are used on big data environments, then query accuracy is maintained, but processing time and resource consumption increase significantly

Engineering Contradiction:

Improvequery accuracyVSAvoidprocessing time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The patent segments the big data environment into a smaller representative subset that captures essential data characteristics. This segmentation allows optimization testing on a manageable scale while preserving the statistical properties needed for accurate query optimization in the full big data environment.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent creates a copy or representation of the big data environment that maintains key structural and statistical properties. This copied environment serves as a testbed for query optimization, enabling accurate optimization without the computational burden of working with the complete big data set.

Inventive Principle:

Principle #26Copying

2Measurement precision

If traditional query optimization methods are used on big data environments, then query accuracy is maintained, but resource consumption increases significantly

Engineering Contradiction:

Improvequery accuracyVSAvoidresource consumption

Core Design Contradiction:

Measurement precisionVSUse of energy by moving object

Solution Approach 1:

The patent segments the big data environment into a smaller representative subset that captures essential data characteristics. This segmentation allows optimization testing on a manageable scale while preserving the statistical properties needed for accurate query optimization in the full big data environment.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent creates a copy or representation of the big data environment that maintains key structural and statistical properties. This copied environment serves as a testbed for query optimization, enabling accurate optimization without the computational burden of working with the complete big data set.

Inventive Principle:

Principle #26Copying

3Productivity

If granular optimization steps are applied to low-performance SQL queries, then query performance may be improved, but the optimization process itself becomes time-consuming

Engineering Contradiction:

Improvequery performanceVSAvoidoptimization time

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent performs preliminary actions by pre-processing the big data environment into a optimized test format that captures essential characteristics. This preliminary preparation enables faster subsequent optimization iterations, as the system doesn't need to repeatedly process the entire big data set during each optimization cycle.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent applies partial action by working with a representative subset of the data rather than the complete set. This partial approach provides sufficient information for effective query optimization while dramatically reducing the time and resources required for the optimization process.

Inventive Principle:

Principle #16Partial or excessive action

Data Source

PatentUS12613865B2Database query optimization

Publication Date: 2026.04.28 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

Computer implemented methods, systems, and computer program products include program code executing on a processor(s) identifies a query with low performance. The program code generates a small data environment for use in optimizing the query. The program code identifies table(s) and field(s) related to the query with low performance. The program code samples a portion of each table of the one or more tables based on, for each table, parameters of the one or more fields in each table, where the portion sampled comprises records from each table with common data traits to a whole of each table. The program code generates a small data environment comprising the portion of each table. The program code performance tests an optimized version of the query by executing it on the small data environment.