Adaptive Indexing for Spreadsheet Data Stores
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Spreadsheets and unstructured data stores face challenges in maintaining query performance as dataset sizes increase, due to the lack of consistent datatypes and indexing strategies typical of traditional databases, which are ineffective in heterogeneous data environments.
Innovation Solution
Implementing an adaptive indexing scheme that creates and maintains index structures in memory for spreadsheet columns, using data-driven and usage-driven approaches to identify which columns to index based on formulas and usage patterns, and providing administrative APIs for manual index management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional database indexing strategies are used in spreadsheets, then query performance can be improved, but the lack of consistent datatypes and heterogeneous data structures make traditional indexing ineffective
Solution Approach 1:
The patent implements dynamic indexing where the system automatically creates and maintains index structures based on detected usage patterns and query frequencies. The index configuration is not static but adapts dynamically to the actual data access patterns, allowing the system to optimize for frequently queried columns while maintaining flexibility for heterogeneous data types.
Solution Approach 2:
The system changes the parameter of index selection by using data-driven approaches to identify which columns should be indexed based on actual usage patterns rather than requiring predetermined schema definitions. This allows the indexing strategy to adapt to varying data types and structures by changing which parameters (columns) are indexed based on empirical evidence.
2Speed
If indexing is implemented for all columns to ensure fast query response, then query speed improves, but memory consumption and system complexity increase
Solution Approach 1:
The patent applies partial indexing by selectively creating index structures only for columns that exhibit high query frequencies or match specific usage patterns. Rather than indexing all columns universally, the system performs partial action on the subset of columns that benefit most from indexing, thereby reducing memory consumption while maintaining query performance for critical operations.
Solution Approach 2:
The system implements feedback-driven indexing where query performance metrics and usage patterns are continuously monitored. Based on this feedback, the system automatically adjusts which columns are indexed, optimizing the balance between query speed and memory usage. Columns that show high query frequency receive indexing resources, while less frequently accessed columns do not consume additional memory.
3Adaptability or versatility
If manual index management through administrative APIs is provided, then flexibility and control improve, but system complexity and operational overhead increase
Solution Approach 1:
The patent implements a universal index management system that combines both automated data-driven indexing and manual administrative APIs into a single multi-functional framework. The same indexing infrastructure supports both automatic pattern-based index creation and manual administrator-controlled index management, allowing the system to serve multiple purposes without requiring separate mechanisms.
Solution Approach 2:
The system provides self-service capabilities where the indexing mechanism automatically monitors its own performance and usage patterns, then autonomously creates or removes indexes based on detected patterns. This self-service approach reduces the need for manual intervention while maintaining the option for administrators to use APIs when needed, thereby reducing operational overhead.
Data Source
AI summary
Aspects described herein implement a data-driven indexing scheme on a spreadsheet-based workbook. One aspect describes a system for implementing an indexing scheme in a spreadsheet workbook. The system comprises a data store, a user interface, and a processor configured to identify a plurality of cells stored in the plurality of tables, analyze individual cells of the plurality of cells to identify at least one formula or expression therein that involve table columns of the plurality of tables stored in the data store, identify the table columns based on parsing the identified at least one formula or expression, generate an index for individual table columns of the identified table columns, receive the query from the user interface, the query including an operator and an identifier for at least one of the table columns, identify and return a result to the query based on the identified index and the operator.