Map-Reduce Query Handling for SQL Database Scalability
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
SQL databases face scalability issues when handling large-scale data, leading to delays in query processing due to the limitations of MDF files and the need for distributed database management systems, which can be costly and inefficient.
Innovation Solution
A method and system for processing SQL database queries using a map-reduce query handling framework that identifies relevant data nodes and executes map-reduce jobs in a distributed manner, allowing for parallel data access and minimizing the load on individual nodes, thereby improving query response times and reducing costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Stability of the object's composition
If SQL databases use MDF files for data storage, then data can be stored in a structured format with metadata, but query processing time increases due to index building and table parsing delays
Solution Approach 1:
The patent segments the monolithic MDF file structure into distributed data blocks across multiple nodes. Each node stores portions of tables in separate blocks, allowing parallel query processing without requiring the entire table to be parsed sequentially. This segmentation eliminates the bottleneck of single-file index building and table parsing while maintaining data structure integrity through distributed metadata management.
Solution Approach 2:
The patent transitions from a single-dimension storage model (single MDF file) to a multi-dimensional distributed architecture where data is organized across multiple nodes and blocks. This dimensional change enables parallel access paths and eliminates sequential processing delays, as queries can be routed to specific nodes containing relevant data blocks without parsing entire tables.
2Quantity of substance
If SQL databases are scaled across multiple computing systems, then data capacity increases, but system complexity and cost increase due to requiring full DBMS on each node
Solution Approach 1:
The patent extracts the heavy DBMS software layer from individual nodes and consolidates it into a central coordinator system. Nodes store and serve data blocks but rely on the coordinator for query parsing, optimization, and result aggregation. This extraction reduces node complexity and cost while maintaining scalability, as nodes become simpler storage-compute units rather than full database servers.
Solution Approach 2:
The patent introduces a coordinator node as an intermediary between clients and data nodes. The coordinator receives queries, generates execution plans, distributes tasks to nodes, and aggregates results. This intermediary layer simplifies the architecture by centralizing complex decision-making logic, allowing nodes to remain simple and cost-effective while still participating in distributed query processing.
3Quantity of substance
If data tables grow large in MDF files, then more data can be stored, but query performance degrades due to increased index building and parsing time
Solution Approach 1:
The patent segments large tables into distributed blocks across multiple nodes, allowing queries to access only relevant portions rather than parsing entire tables. This segmentation maintains high query execution speed even as table size grows, since the system can parallelize access across nodes and avoid unnecessary data processing.
Solution Approach 2:
The patent implements partial action by allowing queries to access only the specific data blocks needed to satisfy the query condition, rather than processing entire tables. The system performs exactly the necessary work (reading only required blocks) and nothing more, maintaining efficient query execution regardless of overall table size.
Data Source
AI summary
Methods and systems for processing a database query are disclosed. An example method includes receiving a SQL database query at a database query handling server, and parsing the SQL database query to identify a database and one or more tables and columns identified by the SQL database query. The method also includes determining a query plan based on the parsed database query. At a database engine, and based on the query plan and the identified database, tables and columns, the method further includes identifying a set of data nodes implicated by the identified database, tables and columns, determining a set of reduce operations and levels at which each of the set of map-reduce operations are to execute, and passing the query plan, the set of data nodes, and the map-reduce operations to a map-reduce query execution framework. The map-reduce query framework returns records as query results to the client system.