An improved method of delta summation for faster current value selection across filtered subsets of interval and temporal relational data

TL;DR

This paper introduces a delta summation technique for relational databases that reduces query execution time and I/O load by representing interval data with delta summaries, especially effective for large, additive, numerical datasets.

Contribution

The paper presents a novel delta summation method that improves current value selection efficiency in relational databases by eliminating sorting, with implementation details and empirical testing across RDBMS platforms.

Findings

Up to 22.4% faster query execution

Reduced I/O load by up to 60.6%

Effective for large, additive numerical data

Abstract

Aggregation in relational databases is accomplished through hashing and sorting interval data, which is computationally expensive and scales poorly as the data volumes grow. In this paper, we show how quantitative interval and time-series data in relational attributes can be represented using delta summary values rather than absolute values. The need for sorting to determine the row corresponding to some maximum timestamp is negated, reducing the time complexity from at least O(n log(n)) towards O(n) and improving query execution times. We illustrate this new method in the relational algebra, present the implementation algorithmically, and test an implementation in two leading RDBMS products against the use of normal equivalents. We found this delta summation technique to be most effective for use cases with additive, numerical data upon which it is necessary to frequently obtain the latest values, and where the row cardinalities are in the order of 10^5. Our experiments found the proposed new delta summation technique could execute faster than the equivalent standard selection method by up to 22.4%, reducing the overall query cost in some circumstances by up to 24.0%, reducing I/O load by up to 60.6%, but with increased query costs for write operations, an increase in CPU time and memory allocation, uncertain performance with very low or very high cardinalities and inconsistent results across different RDBMS platforms.