Database Join Prefetcher for Skewed Data

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database join techniques do not account for data skew in join columns, leading to inefficient processing and reduced performance due to the filtering out of most rows from the outer table, which increases query execution time.

Innovation Solution

A database query processor with a join prefetcher that dynamically prefetches N next join column values from the inner table to skip rows in the outer table, allowing for more efficient query execution by reducing the number of rows processed.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If traditional join techniques are used without considering data skew, then the join operation can be performed using standard algorithms (merge-sort, nested-loop, hash join), but the query execution time increases significantly due to processing and filtering out most rows from the outer table

Engineering Contradiction:

Improvequery execution speedVSAvoidtime spent processing rows that will be filtered out

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent applies preliminary action by prefetching N next join column values from the inner table before actually needing them during the join operation. This allows the system to prepare advance knowledge of upcoming join values, enabling it to skip rows in the outer table that would otherwise be processed and then filtered out. The prefetching occurs in advance of the actual join processing, preventing wasted processing time.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent implements dynamics by making the prefetch count N adjustable and adaptive rather than fixed. The system can dynamically determine and adjust the number of prefetches based on data skew patterns and query characteristics. This dynamic adjustment allows the system to optimize performance for different data distributions and query types, balancing the benefits of skipping rows against the overhead of prefetching operations.

Inventive Principle:

Principle #15Dynamics

2Speed

If the system prefetches more join column values (higher N) to skip more outer table rows, then query execution speed improves, but the complexity of the join processing logic increases

Engineering Contradiction:

Improvequery execution speedVSAvoidcomplexity of join processing logic

Core Design Contradiction:

SpeedVSDevice complexity

Solution Approach 1:

The patent applies segmentation by dividing the join processing into distinct phases: prefetching phase, filtering phase, and actual join phase. The prefetching of N values is separated from the main join logic, allowing the system to handle the complexity of dynamic prefetching independently from the core join algorithms. This segmentation makes the overall system more manageable while maintaining performance benefits.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS11301471B2Database join prefetcher

Publication Date: 2022.04.12 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

A database query processor includes a database join prefetcher that prefetches N next join column values in order to execute the query more efficiently. The prefetched next join column values for an inner table are used to skip rows in an outer table, thereby increasing the performance of executing the query. The number N of prefetches can be dynamically determined and adjusted to improve the performance of executing the query.