Efficient Query Execution Research Articles

DELTA: A Modular, Transparent, and Efficient Synchronization of DLTs and Databases

ABSTRACTBesides cryptocurrencies, DLTs may be also exploited in enterprise systems operated by a consortium of organizations. Their interaction takes usually place on a permissioned blockchain network that holds a set of data to be queried frequently. In this scope, the main problem of DLTs is their unsuitability for a fast service of complex queries on those data. In order to solve this issue, many proposals dump the ledger contents onto databases that, because of their own goals and design, are already optimized for the execution of those queries. Unfortunately, many of those proposals assume that the data to be queried consist in only a block or (cryptocurrency‐related) transaction history. However, those organization consortiums commonly store other structured business‐related information in the DLT, and there is an evident lack of support for querying that other kind of structured data. To remedy those problems, DELTA synchronizes, with minimal overhead, the DLT state into a database, providing (1) a modular architecture with event‐based handling of DLT updates that supports different DLTs and databases, (2) a transparent management, since DLT end users do not need to learn or use any new API in order to handle that synchronization (i.e., those users still rely on the original interface provided by their chosen DLT), (3) the efficient execution of complex queries on those structured data. Thus, DELTA reduces query times up to five orders of magnitude, depending on the DLT and the database, compared to queries directed to the ledger nodes.

SWIX: A Memory-efficient Sliding Window Learned Index

Data stream processing systems enable querying over sliding windows of streams of data. Efficient index structures for the streaming window are a crucial building block to enable querying the sliding window for operations such as aggregation and joins. This paper proposes SWIX, a novel memory-efficient learned index for sliding windows. Unlike conventional learned indexes that rely on tree structures to achieve logarithmic query cost, SWIX has a flat structure that uses substantially less memory and enables efficient query execution while having a low cost for index maintenance when inserting (and retraining). SWIX dynamically adapts itself to the real-time distribution shifts of data streams. SWIX outperforms existing indexes in terms of query execution time and memory footprint for workloads characterized by very frequent updates. Our results show that SWIX has a significantly smaller memory footprint than conventional, streaming, and learned indexes, using only 22% to 42% of the size compared to state-of-the-art approaches, yet outperforming them by up 1.2× to 1.6× on average (and up to 52×) in terms of query time, making it a space- and time-efficient method for indexing data streams. For concurrent learned indexes, Parallel SWIX can achieve up to 3.45× throughput with only 34% of memory consumption.

LPLM: A Neural Language Model for Cardinality Estimation of LIKE-Queries

Cardinality estimation is an important step in cost-based database query optimization. The accuracy of the estimates directly affects the ability of an optimizer to identify the most efficient query execution plan correctly. In this paper, we study cardinality estimation of LIKE-queries, i.e., queries that use the LIKE-operator to match a pattern with wildcards against string-valued attributes. While both traditional and machine-learning-based approaches have been proposed to tackle this problem, we argue that they all suffer from drawbacks. Most importantly, many state-of-the-art approaches are not designed for patterns that contain wildcards in-between characters. Based on past research on neural language models, we introduce the LIKE-Pattern Language Model (LPLM) that uses a new language and a novel probability distribution function to capture the semantics of general LIKE-patterns. We also propose a method to generate training data for our model. We demonstrate that our method outperforms state-of-the-art approaches in terms of precision (Q-error), while offering comparable runtime performance and memory requirements.

Asymptotically Better Query Optimization Using Indexed Algebra

Query optimization is essential for the efficient execution of queries. The necessary analysis, if we can and should apply optimizations and transform the query plan, is already challenging. Traditional techniques focus on the availability of columns at individual operators, which does not scale for analysis of data flow through the query. Tracking available columns per operator takes quadratic space, which can result in multi-second optimization time for deep algebra trees. Instead, we need to re-think the naïve algebra representation to efficiently support data flow analysis. In this paper, we introduce Indexed Algebra , a novel representation of relational algebra that makes common optimization tasks efficient. Indexed Algebra enables efficient reasoning with an auxiliary index structure based on link/cut trees that support dynamic updates and queries in O (log n ). This approach not only improves the asymptotic complexity, but also allows elegant and concise formulations for the data flow questions needed for query optimization. While large queries see theoretically unbounded improvements, Indexed Algebra also improves optimization time of the relatively harmless queries of TPC-H and TPC-DS by more than 1.8×.

Technical Perspective: Revisiting Runtime Dynamic Optimization for Join Queries in Big Data Management Systems

Query optimization is the process of finding an efficient query execution plan for a given SQL query. The runtime difference between a good and a bad plan can be tremendous. For example, in the case of TPC-H query 5, a query with 5 joins, the difference between the best and the worst plan is more than 10,000×. Therefore, it is vital to avoid bad plans. The dominating factor which differentiates a good from a bad plan is their join order and whether this join order avoids large intermediate results.

Robust query processing for linked data fragments

Linked Data Fragments (LDFs) refer to interfaces that allow for publishing and querying Knowledge Graphs on the Web. These interfaces primarily differ in their expressivity and allow for exploring different trade-offs when balancing the workload between clients and servers in decentralized SPARQL query processing. To devise efficient query plans, clients typically rely on heuristics that leverage the metadata provided by the LDF interface, since obtaining fine-grained statistics from remote sources is a challenging task. However, these heuristics are prone to potential estimation errors based on the metadata which can lead to inefficient query executions with a high number of requests, large amounts of data transferred, and, consequently, excessive execution times. In this work, we investigate robust query processing techniques for Linked Data Fragment clients to address these challenges. We first focus on robust plan selection by proposing CROP, a query plan optimizer that explores the cost and robustness of alternative query plans. Then, we address robust query execution by proposing a new class of adaptive operators: Polymorphic Join Operators. These operators adapt their join strategy in response to possible cardinality estimation errors. The results of our first experimental study show that CROP outperforms state-of-the-art clients by exploring alternative plans based on their cost and robustness. In our second experimental study, we investigate how different planning approaches can benefit from polymorphic join operators and find that they enable more efficient query execution in the majority of cases.

Optimizing storage of RDF archives using bidirectional delta chains

Linked Open Datasets on the Web that are published as RDF can evolve over time. There is a need to be able to store such evolving RDF datasets, and query across their versions. Different storage strategies are available for managing such versioned datasets, each being efficient for specific types of versioned queries. In recent work, a hybrid storage strategy has been introduced that combines these different strategies to lead to more efficient query execution for all versioned query types at the cost of increased ingestion time. While this trade-off is beneficial in the context of Web querying, it suffers from exponential ingestion times in terms of the number of versions, which becomes problematic for RDF datasets with many versions. As such, there is a need for an improved storage strategy that scales better in terms of ingestion time for many versions. We have designed, implemented, and evaluated a change to the hybrid storage strategy where we make use of a bidirectional delta chain instead of the default unidirectional delta chain. In this article, we introduce a concrete architecture for this change, together with accompanying ingestion and querying algorithms. Experimental results from our implementation show that the ingestion time is significantly reduced. As an additional benefit, this change also leads to lower total storage size and even improved query execution performance in some cases. This work shows that modifying the structure of delta chains within the hybrid storage strategy can be highly beneficial for RDF archives. In future work, other modifications to this delta chain structure deserve to be investigated, to further improve the scalability of ingestion and querying of datasets with many versions.

Настройка критериев планировщика СУБД с учётом динамической компиляции

Dynamic compilation of certain operator compositions might have a drastic impact on overall query performance, but not be considered during optimal query plan selection by DBMS planner due to lack of knowledge. To tackle this problem, we propose to extend cost model with criteria that make dynamic compilation overhead relevant. The necessity to set up the optimizer criteria is due to the fact that the properties of various execution models impose restrictions on the efficiency of query plan execution using certain operator nodes. For example, the push-model used in the dynamic compiler is advantageous when executing queries using sequential scan. So, dynamic compilation makes sequential scan more efficient than index scan. Using index nodes in such a plan makes the value of the dynamic compilation method diminishing. To overcome these problems, it is proposed to configure the DBMS optimizer, so that it evaluates and takes into account the efficiency of using certain types of nodes when building a query plan with its subsequent dynamic compilation. This paper discusses the modification of the PostgreSQL planner to select the most efficient query execution plan based on hardware characteristics and the execution model of operator nodes with interpretation or compilation.

An Efficient Distributed SPARQL Query Processing Scheme Considering Communication Costs in Spark Environments

Various distributed processing schemes were studied to efficiently utilize a large scale of RDF graph in semantic web services. This paper proposes a new distributed SPARQL query processing scheme considering communication costs in Spark environments to reduce I/O costs during SPARQL query processing. We divide a SPARQL query into several subqueries using a WHERE clause to process a query of an RDF graph stored in a distributed environment. The proposed scheme reduces data communication costs by grouping the divided subqueries in related nodes through the index and processing them, and the grouped subqueries calculate the cost of all possible query execution paths to select an efficient query execution path. The efficient query execution path is selected through the algorithm considering the data parsing cost of all possible query execution paths, amount of data communication, and queue time per node. It is shown through various performance evaluations that the proposed scheme outperforms the existing schemes.

Hybrid e-Government Framework based on Datawarehousing and MAS for Data Interoperability

The exponential growth in technological innovation is driven in large part by the digitization of multiple domains and assumes environments of increasing data volumes, arriving at high velocity and variety. e-Government is one such domain that exploits current ICT innovations to improve the delivery of public services to its citizens, businesses, and other stakeholders This imposes to continuously maintain information on daily operations, activities, and assets as well as extensive profiles on citizens, institutions, and organizations. In addition, current centralized platform-based approaches suffer from the single-point-of-failure, which may result in data breaches and leakages, leading to the need for efficient robust mechanisms to ensure secure information sharing, data interoperability, and privacy. In this paper, we propose a business intelligence approach to design a data interoperability framework for e-governance based on data warehousing technology to improve transparency and data accessibility. We also present a hybrid data filtering mechanism, which relies both on the Extraction, Transformation, and Loading (ETL) process, and multi-agent technology to integrate data quality and data interoperability, and supports data transformation into human-readable format. Finally, the framework emphasizes the availability of materialized views to enable efficient execution of analytical queries directly on the large volumes of raw data in the data warehouse.

An automatic clustering technique for query plan recommendation

The query optimizer is responsible for identifying the most efficient Query Execution Plans (QEP’s). The distributed database relations may be kept in several places. These results in a dramatic increase in the number of alternative query’ plans. The query optimizer cannot exhaustively explore the alternative query plans in a vast search space at reasonable computational costs. Henceforth, reusing the previously generated plans instead of generating new plans for new queries is an efficient technique for query processing. To improve the accuracy of clustering, we’ve rewritten the queries to standardize their structures. Furthermore, TF representation schema has been used to convert the queries into vectors. In this paper, we’ve introduced a multi-objective automatic query plan recommendation method, a combination of incremental DBSCAN and NSGA-II. The quality of the results of incremental DBSCAN has been influenced by Minpts (minimum points) and Eps (epsilon). Two cluster validity indices, Dunn index and Davies–Bouldin index, have simultaneously been optimized to calculate the goodness of an answer. Comparative results have been shown against the incremental DBSCAN and K-means regarding an external cluster validity index, namely, the ARI. By comparing different types of query workloads, we’ve found that the introduced method outperforms the other well-known approaches.

Efficient Execution of Complex Context Queries to Enable Near Real-Time Smart IoT Applications.

As the Internet of Things (IoT) is evolving at a fast pace, the need for contextual intelligence has become more crucial for delivering IoT intelligence, efficiency, effectiveness, performance, and sustainability. Contextual intelligence enables interactions between IoT devices such as sensors/actuators, smartphones and connected vehicles, to name but a few. Context management platforms (CMP) are emerging as a promising solution to deliver contextual intelligence for IoT. However, the development of a generic solution that allows IoT devices and services to publish, consume, monitor, and share context is still in its infancy. In this paper, we propose, validate and explain the details of a novel mechanism called Context Query Engine (CQE), which is an integral part of a pioneering CMP called Context-as-a-Service (CoaaS). CQE is responsible for efficient execution of context queries in near real-time. We present the architecture of CQE and illuminate its workflows. We also conduct extensive experimental performance and scalability evaluation of the proposed CQE. Results of experimental evaluation convincingly demonstrate that CoaaS outperforms its competitors in executing complex context queries. Moreover, the advanced functionality of the embedded query language makes CoaaS a decent candidate for real-life deployments.

Search and analytics using semantic annotations

Search and analytics using semantic annotations

A spatially-pruned vertex expansion operator in the Neo4j graph database system

Graphs are widely used to model data in many application domains. Thanks to the wide spread use of GPS-enabled devices, many applications assign spatial attributes to graph vertexes (e.g., geographic knowledge bases, geo-tagged social media). Graph database systems such as Neo4j and Titan are commonly used to manage and query graph data. Even though an off-the-shelf graph database system allows users to define spatial location semantics on vertexes and edges, existing graph query processors are not natively optimized for spatial predicates. The paper proposes GeoExpand—a query operator that adds spatial data awareness to a graph database management system. GeoExpand allows efficient execution of graph queries that involve spatial predicates. The proposed operator makes use of spatial bitmap entries stored as properties in the graph. GeoExpand leverages such bitmap entries to possibly terminate the graph traversal process early and hence reduces the query latency. Since the spatial bitmap entries are represented using a light-weight data structure, they do not add a lot of storage or maintenance overhead. That makes GeoExpand a practical solution. Experiments based on implementation inside the core kernel of Neo4j prove that the GeoExpand operator exhibits up to two orders of magnitude better query response time than the classic Expand operator used in Neo4j.

A comparative evaluation of order-revealing encryption schemes and secure range-query protocols

Database query evaluation over encrypted data can allow database users to maintain the privacy of their data while outsourcing data processing. Order-Preserving Encryption (OPE) and Order-Revealing Encryption (ORE) were designed to enable efficient query execution, but provide only partial privacy. More private protocols, based on Searchable Symmetric Encryption (SSE), Oblivious RAM (ORAM) or custom encrypted data structures, have also been designed. In this paper, we develop a framework to provide the first comprehensive comparison among a number of range query protocols that ensure varying levels of privacy of user data. We evaluate five ORE-based and five generic range query protocols. We analyze and compare them both theoretically and experimentally and measure their performance over database indexing and query evaluation. We report not only execution time but also I/O performance, communication amount, and usage of cryptographic primitive operations. Our comparison reveals some interesting insights concerning the relative security and performance of these approaches in database settings.

Bulk-loading and bulk-insertion algorithms for $$\hbox {xBR}^{+}\hbox {-trees}$$ xBR + -trees in Solid State Drives

Spatial indexes are important in spatial databases for efficient execution of queries involving spatial constraints. The $$\hbox {xBR}^{+}\hbox {-tree}$$ is a balanced, disk-resident, Quadtree-based index for point data, which is very efficient for processing spatial queries. Bulk-loading refers to the process of creating an index from scratch as a whole, when the dataset to be indexed is available beforehand, instead of creating (loading) the index gradually, when the dataset items are available one-by-one. Bulk insertion refers to the process of updating an existing index by inserting a large batch of new data, treating the items of this batch as a whole and not by inserting these items one-by-one. In this paper, we modify previous bulk-loading and bulk-insertion algorithms for $$\hbox {xBR}^{+}\hbox {-trees}$$ to achieve higher performance by taking advantage of the special features of Solid State Drives (SSDs). SSDs have attracted database developers, mainly due to their higher read performance (thanks to their internal parallelism) than Hard Disk Drives. Using real and artificial datasets of various cardinalities, we experimentally compare the modified algorithms against their predecessors and show that the modified algorithms are clear winners regarding performance.

Generating custom code for efficient query execution on heterogeneous processors

Processor manufacturers build increasingly specialized processors to mitigate the effects of the power wall in order to deliver improved performance. Currently, database engines have to be manually optimized for each processor which is a costly and error- prone process. In this paper, we propose concepts to adapt to and to exploit the performance enhancements of modern processors automatically. Our core idea is to create processor-specific code variants and to learn a well-performing code variant for each processor. These code variants leverage various parallelization strategies and apply both generic- and processor-specific code transformations. Our experimental results show that the performance of code variants may diverge up to two orders of magnitude. In order to achieve peak performance, we generate custom code for each processor. We show that our approach finds an efficient custom code variant for multi-core CPUs, GPUs, and MICs.

Smart scheme: an efficient query execution scheme for event-driven stream processing

With the increase in stream data, a demand for stream processing has become diverse and complicated. To meet this demand, several stream processing engines (SPEs) have been developed which execute continuous queries (CQs) to process continuous data streams. Event-driven stream processing, which is one of the important requirements, continuously gets the incoming stream data and, however, generates query results only on the occurrence of specified events. In the basic query execution scheme, even when no event is raised, input stream tuples are continuously processed by query operators, though they do not generate any query result. This results in increased system load and wastage of system resources. For this problem, we propose a smart event-driven stream processing scheme, which makes use of smart windows to buffer the stream tuples during the absence of an event. When the event is raised, the buffered tuples are flushed and processed by the downstream operators. If the buffered tuples in the smart window expire due to the window size before the occurrence of an event, they are deleted directly from the smart window. Since CQs once registered are executed for several weeks, months or even years, SPEs usually execute several CQs in parallel and merge their query plans whenever possible to save processing cost. Due to the presence of smart window, existing multi-query optimization techniques cannot work for smart event-driven stream processing. Hence, this work proposes a multi-query optimization for the proposed smart scheme to cover the cases where multiple continuous queries are registered. Extensive experiments are performed on real and synthetic data streams to show the effectiveness of the proposed smart scheme and its multi-query optimization.

Efficient query processing on large spatial databases: A performance study

A spatial index, called xBR+-tree, is compared against the R*-tree and the R+-tree.Performance of tree building and processing of common spatial queries is studied.Existing and new algorithms for processing these queries are utilized.Extensive experimental results on medium and large datasets are presented.The xBR+-tree is the winner of execution-time in all cases and of I/0 in most cases. Processing of spatial queries has been studied extensively in the literature. In most cases, it is accomplished by indexing spatial data using spatial access methods. Spatial indexes, such as those based on the Quadtree, are important in spatial databases for efficient execution of queries involving spatial constraints and objects. In this paper, we study a recent balanced disk-based index structure for point data, called xBR+-tree, that belongs to the Quadtree family and hierarchically decomposes space in a regular manner. For the most common spatial queries, like Point Location, Window, Distance Range, Nearest Neighbor and Distance-based Join, the R-tree family is a very popular choice of spatial index, due to its excellent query performance. For this reason, we compare the performance of the xBR+-tree with respect to the R*-tree and the R+-tree for tree building and processing the most studied spatial queries. To perform this comparison, we utilize existing algorithms and present new ones. We demonstrate through extensive experimental performance results (I/O efficiency and execution time), based on medium and large real and synthetic datasets, that the xBR+-tree is a big winner in execution time in all cases and a winner in I/O in most cases.

Dynamic spatial index for efficient query processing on the cloud

Data owners with large volumes of data can outsource spatial databases by taking advantage of the cost-effective cloud computing model with attractive on-demand features such as scalability and high computing power. Data confidentiality in outsourced databases is a key requirement and therefore, untrusted third-party service providers in the cloud should not be able to view or manipulate the data. This paper proposes DISC (Dynamic Index for Spatial data on the Cloud), a secure retrieval scheme to answer range queries over encrypted databases at the Cloud Service Provider. The dynamic spatial index is also able to support dynamic updates on the outsourced data at the cloud server. To be able to support secure query processing and updates on the Cloud, spatial transformation is applied to the data and the spatial index is encrypted using Order-Preserving Encryption. With transformation and cryptography techniques, DISC achieves a balance between efficient query execution and data confidentiality in a cloud environment. Additionally, a more secure scheme, DISC ∗, is proposed to balance the trade-off between query results returned and security provided. The security analysis section studies the various attacks handled by DISC. The experimental study demonstrates that the proposed scheme achieves a lower communication cost in comparison to existing cloud retrieval schemes.