Query Batching for One-to-One and One-to-Many Relationships
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current transactional prefetching methods, such as join-based and page-based prefetching, are inefficient for one-to-one and one-to-many relationships, leading to unnecessary data repetition and increased overhead due to multiple round-trip interactions between application and database servers.
Innovation Solution
A method that identifies a transaction and a prefetch assertion, merges correlated queries, and transmits them as a batch to the database server, reducing the need for multiple round-trips by utilizing parameter and result correlation within a named transaction.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If join-based prefetching is used to reduce round-trip interactions, then the number of round-trips between application server and database server is reduced, but data from the many side of one-to-many relationships is repeated in multiple result rows
Solution Approach 1:
The patent segments the query execution into multiple independent queries within a batch, where each query targets a specific relationship type (one-to-one, one-to-many, many-to-one) with optimized joining strategies for each segment, preventing unnecessary data repetition while maintaining reduced round-trip interactions
Solution Approach 2:
The patent applies different prefetching strategies to different relationship types locally: using appropriate join methods for one-to-one relationships, optimized one-to-many joins that prevent duplication, and reverse joins for many-to-one relationships, rather than applying a single uniform join-based approach to all relationships
2Productivity
If join-based prefetching is enabled at the level of defined relationships, then query results can be fetched in advance, but overhead increases when certain information is required in one transaction but not another involving the same tables
Solution Approach 1:
The patent dynamically determines which prefetching strategies to apply based on the specific transaction's requirements and the relationship types involved, rather than using static relationship-level enablement. The system adapts the prefetching approach transaction-by-transaction, reducing overhead by only applying prefetching when beneficial
Solution Approach 2:
The patent creates a universal query batching framework that handles multiple relationship types (one-to-one, one-to-many, many-to-one) and multiple prefetching strategies within a single transactional context, allowing the system to serve different transaction needs with a unified approach rather than requiring separate configurations
3Loss of time
If page-based prefetching is used to fetch parent and child objects in the same database page, then round-trips to fetch child objects are avoided, but the technique does not work well for transactions that do not use child objects and does not work at all for many-to-one relationships
Solution Approach 1:
The patent develops a universal query batching system that works across all relationship types (one-to-one, one-to-many, many-to-one) and all transaction scenarios (with or without child object usage), replacing the relationship-specific page-based approach with a relationship-agnostic batching framework that adapts to each transaction's needs
Data Source
AI summary
A system, method, and computer program product for performing transactional prefetching are presented. Steps for sending correlated queries to a database server for processing are disclosed.