Query Execution Time Research Articles

Performance Study of Time Series Databases

The growth of big-data sectors such as the Internet of Things (IoT) generates enormous volumes of data. As IoT devices generate a vast volume of time-series data, the Time Series Database (TSDB) popularity has grown alongside the rise of IoT. Time series databases are developed to manage and analyze huge amounts of time series data. However, it is not easy to choose the best one from them. The most popular benchmarks compare the performance of different databases to each other but use random or synthetic data that applies to only one domain. As a result, these benchmarks may not always accurately represent real-world performance. It is required to comprehensively compare the performance of time series databases with real datasets. The experiment shows significant performance differences for data injection time and query execution time when comparing real and synthetic datasets. The results are reported and analyzed.

Researching basic searchable encryption schemes in databases that support SQL

Currently, many users prefer to outsource data to third-party cloud servers in order to mitigate the load of local storage. However, storing sensitive data on remote servers creates security challenges and is a source of concern for data owners. With ever-growing security and privacy concerns, it is becoming increasingly important to encrypt data stored remotely. However, the use of traditional encryption prevents the search operation in the encrypted data. One approach to solving this problem is searchable encryption. Solutions for search in secure databases cover a wide range of cryptographic techniques, although there is still no dominant solution. Designing secure search systems is a balance between security, functionality, performance, and usability. Therefore, this paper provides an overview of some of the important current secure search solutions. The main searchable encryption systems of databases that support SQL are considered. The strengths and weaknesses of the analyzed systems and the techniques implemented in them are highlighted. A comparative analysis of some characteristics of the compared systems is given. Attention is drawn to the fact that the ability to perform search operations in encrypted data leads to a complication of systems, an increase in the amount of required memory and query execution time. All this indicates the openness of the protected search problem and the need for further research in this direction to ensure secure work with remote databases and data warehouses.

Automatic index selection with learned cost estimator

Choosing useful indexes for a relational database is important for efficient query optimization. However, current methods tend to use the cost estimated by the optimizer in the database management system (DBMS) to measure the benefits of the index and, due to inaccurate cost estimates, do not bring to the most optimal solution. In addition, existing reinforcement learning methods treat the creation of different indexes as independent actions, ignoring the relationships among indexes, which may bring to unnecessary training costs. To address these problems, we propose DeepIndex, an automatic index selector with a learning-based cost estimator to improve the quality of index selection. To accurately estimate the benefit of an index, we design a learning-based cost estimator to predict the execution time of queries on certain indexes. In particular, we treat query plans as graphs and develop a model based on graph convolutional network (GCN) to learn features from queries and indexes. After that, we design a reinforcement-learning-based index selection model considering the relationships among indexes and combine our cost estimator to select indexes. Extensive experiments on two benchmark workloads JOB and TPC-DS show that our model performs better than state-of-the-art models with the lowest storage costs, and our lowest relative execution cost outperforms the baselines by 38.59% on JOB and 10.42% on TPC-DS.

An Efficient Source Selection Approach for Retrieving Electronic Health Records From Federated Clinical Repositories

The data retrieval and unification of patient electronic health records from distinct clinical repositories is essential for an effective health decision making. The scattering of patient data in many complementary but also overlapping sources poses a challenge in locating the desired records. Maintaining the patient master index also faced legal and privacy issues. Therefore, it is essential to locate the relevant records of the patient in federated clinical data sources. The research carried out proposed an efficient approach for relevant selection of patient records using semantic technologies. The approach uses both triple pattern-wise and join-aware source selection approaches for optimal selection of relevant data sources. The state-of-art federated engines are evaluated on the basis of source selection time and overall query execution time for different federated queries. The experimental results shows that the proposed approach selects the relevant data sources with reduced number of remote requests and significantly reduces the query execution time.

A scalable and generic approach to range joins

Analytical database systems provide great insights into large datasets and are an excellent tool for data exploration and analysis. A central pillar of query processing is the efficient evaluation of equi-joins, typically with linear-time algorithms (e.g. hash joins). However, for many use-cases with location and temporal data, non-equi joins, like range joins, occur in queries. Without optimizations, this typically results in nested loop evaluation with quadratic complexity. This leads to unacceptable query execution times. Different mitigations have been proposed in the past, like partitioning or sorting the data. While these allow for handling certain classes of queries, they tend to be restricted in the kind of queries they can support. And, perhaps even more importantly, they do not play nice with additional equality predicates that typically occur within a query and that have to be considered, too. In this work, we present a kd-tree-based, multi-dimension range join that supports a very wide range of queries, and that can exploit additional equality constraints. This approach allows us to handle large classes of queries very efficiently, with negligible memory overhead, and it is suitable as a general-purpose solution for range queries in database systems. The join algorithm is fully parallel, both during the build and the probe phase, and scales to large problem instances and high core counts. We demonstrate the feasibility of this approach by integrating it into our database system Umbra and performing extensive experiments with both large real world data sets and with synthetic benchmarks used for sensitivity analysis. In our experiments, it outperforms hand-tuned Spark code and all other database systems that we have tested.

Query processing on tensor computation runtimes

The huge demand for computation in artificial intelligence (AI) is driving unparalleled investments in hardware and software systems for AI. This leads to an explosion in the number of specialized hardware devices, which are now offered by major cloud vendors. By hiding the low-level complexity through a tensor-based interface, tensor computation runtimes (TCRs) such as PyTorch allow data scientists to efficiently exploit the exciting capabilities offered by the new hardware. In this paper, we explore how database management systems can ride the wave of innovation happening in the AI space. We design, build, and evaluate Tensor Query Processor (TQP): TQP transforms SQL queries into tensor programs and executes them on TCRs. TQP is able to run the full TPC-H benchmark by implementing novel algorithms for relational operators on the tensor routines. At the same time, TQP can support various hardware while only requiring a fraction of the usual development effort. Experiments show that TQP can improve query execution time by up to 10X over specialized CPU- and GPU-only systems. Finally, TQP can accelerate queries mixing ML predictions and SQL end-to-end, and deliver up to 9X speedup over CPU baselines.

Query Execution Time Analysis Using Apache Spark Framework for Big Data: A CRM Approach

Customer Relationship Management (CRM) is a systematic way of working with current and prospective customers to manage long-term relationships and interactions between the company and customers. Recently, Big Data has become a buzzword. It consists of huge data repositories, having information collected from online and offline resources, and it is hard to process such datasets with traditional data processing tools and techniques. The presented research work tries to explore the potential of Big Data to create, optimise and transform an insightful customer relationship management system by analysing large amount of datasets for enhancing customer life cycle profitability. In this research work, a dataset, “Book Crossing” is used for Big Data processing and execution time analysis for simple and complex SQL queries. This research tries to analyse the impact of data size on the query execution time for one of the majorly used Big Data frameworks, namely Apache Spark. It is a recently developed in-memory Big Data processing framework with a SPARK SQL module for efficient SQL query execution. It has been found that Apache-Spark gives better results with large size datasets compare to small size datasets and fares better as compared to Hadoop, one of the majorly used Big Data Frameworks (based on qualitative analysis).

A Comprehensive Spark-Based Layer for Converting Relational Databases to NoSQL

Currently, the continuous massive growth in the size, variety, and velocity of data is defined as big data. Relational databases have a limited ability to work with big data. Consequently, not only structured query language (NoSQL) databases were utilized to handle big data because NoSQL represents data in diverse models and uses a variety of query languages, unlike traditional relational databases. Therefore, using NoSQL has become essential, and many studies have attempted to propose different layers to convert relational databases to NoSQL; however, most of them targeted only one or two models of NoSQL, and evaluated their layers on a single node, not in a distributed environment. This study proposes a Spark-based layer for mapping relational databases to NoSQL models, focusing on the document, column, and key–value databases of NoSQL models. The proposed Spark-based layer comprises of two parts. The first part is concerned with converting relational databases to document, column, and key–value databases, and encompasses two phases: a metadata analyzer of relational databases and Spark-based transformation and migration. The second part focuses on executing a structured query language (SQL) on the NoSQL. The suggested layer was applied and compared with Unity, as it has similar components and features and supports sub-queries and join operations in a single-node environment. The experimental results show that the proposed layer outperformed Unity in terms of the query execution time by a factor of three. In addition, the proposed layer was applied to multi-node clusters using different scenarios, and the results show that the integration between the Spark cluster and NoSQL databases on multi-node clusters provided better performance in reading and writing while increasing the dataset size than using a single node.

A Comprehensive Empirical Study of Query Performance Across GPU DBMSes

In recent years, GPU database management systems (DBMSes) have rapidly become popular largely due to their remarkable acceleration capability obtained through extreme parallelism in query evaluations. However, there has been relatively little study on the characteristics of these GPU DBMSes for a better understanding of their query performance in various contexts. To fill this gap, we have conducted a rigorous empirical study to identify such factors and to propose a structural causal model, including key factors and their relationships, to explicate the variances of the query execution times on the GPU DBMSes. To test the model, we have designed and run comprehensive experiments and conducted in-depth statistical analyses on the obtained data. As a result, our model achieves about 77% amount of variance explained on the query time and indicates that reducing kernel time and data transfer time are the key factors to improve the query time. Also, our results show that the studied systems still need to resolve several concerns such as bounded processing within GPU memory, lack of rich query evaluation operators, limited scalability, and GPU under-utilization.

VIP hashing

All data is not equally popular. Often, some portion of data is more frequently accessed than the rest, which causes a skew in popularity of the data items. Adapting to this skew can improve performance, and this topic has been studied extensively in the past for disk-based settings. In this work, we consider an in-memory data structure, namely hash table , and show how one can leverage the skew in popularity for higher performance. Hashing is a low-latency operation, sensitive to the effects of caching and code complexity, among other factors. These factors make learning in-the-loop challenging as the overhead of performing additional operations can have significant impact on performance. In this paper, we propose VIP hashing, a hash table method that uses lightweight mechanisms for learning the skew in popularity and adapting the hash table layout on the fly. These mechanisms are non-blocking, i.e, the hash table is operational at all times. The overhead is controlled by sensing changes in the popularity distribution to dynamically switch-on/off the mechanisms as needed. We ran extensive tests against a host of workloads generated by Wiscer , a homegrown benchmarking tool, and we find that VIP hashing improves performance in the presence of skew (22% increase in fetch operation throughput for a hash table with 1M keys under low skew) while adapting to insert and delete operations, and changing popularity distribution of keys on the fly. Our experiments on DuckDB show that VIP hashing reduces the end-to-end execution time of TPC-H query 9 by 20% under low skew.

Columnar formats for schemaless LSM-based document stores

In the last decade, document store database systems have gained more traction for storing and querying large volumes of semi-structured data. However, the flexibility of the document stores' data models has limited their ability to store data in a column-major layout --- making them less performant for analytical workloads than column store relational databases. In this paper, we propose several techniques based on piggy-backing on Log-Structured Merge (LSM) tree events and tailored to document stores to store data in a columnar layout. We first extend the Dremel format, a popular on-disk columnar format for semi-structured data, to comply with document stores' flexible data model. We then introduce a new columnar layout for organizing and storing data in LSM-based storage. We also highlight the potential of using query compilation techniques for document stores, where values' types are known only at runtime. We have implemented and evaluated our techniques to measure their impact on storage, data ingestion, and query performance in Apache AsterixDB. Our experiments show significant performance gains, improving the query execution time by orders of magnitude while minimally impacting ingestion performance.

Network Cost-Aware Geo-Distributed Data Analytics System

Many geo-distributed data analytics (GDA) systems have focused on the network performance-bottleneck: inter-data center network bandwidth to improve performance. Unfortunately, these systems may encounter a <i>cost-bottleneck</i> ( <inline-formula><tex-math notation="LaTeX">${\$}$</tex-math></inline-formula> ) because they have not considered data transfer cost ( <inline-formula><tex-math notation="LaTeX">${\$}$</tex-math></inline-formula> ), one of the most expensive and heterogeneous resources in a multi-cloud environment. In this article, we present <i>Kimchi</i> , a network cost-aware GDA system to meet the cost-performance tradeoff by exploiting data transfer cost heterogeneity to avoid the cost-bottleneck. Kimchi determines cost-aware task placement decisions for scheduling tasks given inputs including data transfer cost, network bandwidth, input data size and locations, and desired cost-performance tradeoff preference. In addition, Kimchi is also mindful of data transfer cost in the presence of dynamics. Kimchi has been applied to two common GDA MapReduce models: synchronous barrier and asynchronous push-based shuffle. A Kimchi prototype has been implemented on Spark, and experiments show that it reduces cost by 5% <inline-formula><tex-math notation="LaTeX">$\scriptstyle \sim$</tex-math></inline-formula> 24% without impacting performance and reduces query execution time by 45% <inline-formula><tex-math notation="LaTeX">$\scriptstyle \sim$</tex-math></inline-formula> 70% without impacting cost compared to other baseline approaches centralized, vanilla Spark, and bandwidth-aware (e.g., Iridium). More importantly, Kimchi allows applications to explore a much richer cost-performance tradeoff space in a multi-cloud environment.

PERFORMANCE STUDY OF THE DTU MODEL FOR RELATIONAL DATABASES ON THE AZURE PLATFORM

When solving problems of working with relational databases on cloud platforms, the problem arises of choosing a specific model to ensure the performance of executing queries of varying complexity. The object of research is the processes of implementing various types of queries to relational databases within the framework of the DTU purchase model of the MS Azure platform. The subject is methods for evaluating the performance of work with relational databases based on the timing of query execution and indicators of the load on the resources of the cloud platform. The aim of the study is to develop a system of indicators for monitoring the current state of work with the database for reasonable decision-making on the choice of a certain price category of the DTU model of the MS Azure cloud service, which will optimize the results of working with the database. platforms Achieving the set goals involves the following tasks: to analyze modern tools and services for working with databases, in particular relational databases, on Azure and AWS cloud platforms, the features of their application and implementation; develop software for generating test relational databases of different sizes; test the generated databases on a local resource; taking into account the characteristics of the levels of the Azure DTU model, develop a new system of performance indicators, which includes 2 groups - time indicators and indicators of the load on existing platform resources; develop and implement queries of varying complexity for the generated test database for different levels of the DTU model and analyze the results. Methods. The following methods were used in the research: methods of relational database design; methods of creating queries in SQL-oriented databases with any number of tables; methods of creating and migrating data to cloud platforms; methods of monitoring the results of queries based on time and resource indicators; methods of generating test data for relational databases; system approach for complex assessment and analysis of productivity of work with relational databases. Results. On the basis of the developed scorecard used for the current analysis of the processes of working with relational databases of the MS Azure platform, numerous experiments were carried out for different levels of the model for simple and complex queries to a database with a total volume of 20 GB: loading of DTU model levels when executing various queries, the influence of model levels DTU Azure SQL database on the performance of simple and complex queries, the dependence of the execution time of various queries on the load of the CPU and the speed of write/read operations for different levels of the model. Conclusions. The results of the experiments allow us to conclude that the levels of the DTU model - S3 and S7 - are used to generate test data of various sizes (up to 20 GB) and execute database queries. The practical use of the proposed indicators to evaluate the results of applying the DTU model will improve the efficiency of decision-making on choosing the model level when implementing various queries and generating test data on the Azure cloud platform. The developed set of indicators for working with relational databases on the Azure cloud platform expands the basis of the methodological framework for evaluating the performance of working with relational databases on cloud platforms by analyzing the results of executing the simple and complex database queries on the resources involved.

Graph data temporal evolutions: From conceptual modelling to implementation

Graph data management systems are designed for managing highly interconnected data. However, most of the existing work on the topic does not take into account the temporal dimension of such data, even though they may change over time: new interconnections, new internal characteristics of data (etc.). For decision makers, these data changes provide additional insights to explain the underlying behaviour of a business domain. The objective of this paper is to propose a complete solution to manage temporal interconnected data. To do so, we propose a new conceptual model of temporal graphs. It has the advantage of being generic as it captures the different kinds of changes that may occur in interconnected data. We define a set of translation rules to convert our conceptual model into the logical property graph. Based on the translation rules, we implement several temporal graphs according to benchmark and real-world datasets in the Neo4j data store. These implementations allow us to carry out a comprehensive study of the feasibility and usability (through business analyses), the efficiency (saving up to 99% query execution times comparing to classic approaches) and the scalability of our solution.

Efficient semantic summary graphs for querying large knowledge graphs

Knowledge Graphs (KGs) integrate heterogeneous data, but one challenge is the development of efficient tools for allowing end users to extract useful insights from these sources of knowledge. In such a context, reducing the size of a Resource Description Framework (RDF) graph while preserving all information can speed up query engines by limiting data shuffle, especially in a distributed setting. This paper presents two algorithms for RDF graph summarization: Grouping Based Summarization (GBS) and Query Based Summarization (QBS). The latter is an optimized and lossless approach for the former method. We empirically study the effectiveness of the proposed lossless RDF graph summarization to retrieve complete data, by rewriting an RDF Query Language called SPARQL query with fewer triple patterns using a semantic similarity. We conduct our experimental study in instances of four datasets with different sizes. Compared with the state-of-the-art query engine Sparklify executed over the original RDF graphs as a baseline, QBS query execution time is reduced by up to 80% and the summarized RDF graph is decreased by up to 99%.

Secure Network Channel for Web Services based on SSL/TLS Technology in a Linux Environment

An approach to the organization of secure interaction in distributed systems via a public network is considered, based on the organization of secure communication channels based on sSl/TLS technology. Unlike VPN technology, the described approach is strictly focused on supporting only HTTP/SOAP interactions in distributed systems, which allows you to implement authentication and authorization based on HTTP-header data and client public key certifi­cates as ready-made technical solutions. The approach implies the use of special gateways that provide switching from HTTP to HTTPS on the client side and switching from HTTPS to HTTP on the web server side and make up a "transparent" communication channel for system components. It is assumed that both client programs and web serv­ers are located in the same secure private network (or even on the same network node) with the gateways serving them, and only the interaction between the gateways is carried out through the public network. The work of gateways is based on the use of SSL/TLS technology to add a secure channel over an already open TCP connection. The main idea of the approach is that in this case, security tools are connected at high levels of the OSI protocol hierarchy, which allows gateways to analyze high-level parameters of information requests and responses of web servers con­tained in HTTP-headers. And this, in turn, allows you to add additional "intelligence" to the gateways associated with authentication of servers and clients, as well as with the differentiation of access rights to information resources up to individual functions (methods) of web services based on the data contained in "Subject Name" attribute of public key certificates. The implementation of the approach in the Linux environment and the results of an experimental study are described. In particular, the study showed that when calling service functions with a runtime of 0.5 seconds or higher, the secure channel increases the total query execution time by only a few percent, even with a rather large amount of data being transmitted (up to 200 kilobytes).

Translating a Distributed Relational Database to a Document Database

The problem of transferring data from a database of one type to a database of another type is relevant today due to the increase in data volumes and the complexity of tasks solved in various fields of human activity. The existing databases are less and less satisfying the needs of users. New types of databases appear that are more suitable for solving large-scale problems. This article proposes an algorithm for solving the problem of optimizing the document structure of a database based on metadata about the structure of a distributed relational database from which data are transferred. The approach also takes into account information about the structure of the priority database queries. The priority of database queries is user-defined. The system of automatic translation of the database, taking into account these metadata, allows the user to create a distributed document database that is optimal in two parameters: in terms of the volume of stored data and in terms of the execution time of priority database queries.

A Comprehensive Empirical Study of Query Performance Across GPU DBMSes

In recent years, GPU database management systems (DBMSes) have rapidly become popular largely due to their remarkable acceleration capability obtained through extreme parallelism in query evaluations. However, there has been relatively little study on the characteristics of these GPU DBMSes for a better understanding of their query performance in various contexts. Also, little has been known about what the potential factors could be that affect the query processing jobs within the GPU DBMSes. To fill this gap, we have conducted a study to identify such factors and to propose a structural causal model, including key factors and their relationships, to explicate the variances of the query execution times on the GPU DBMSes. We have also established a set of hypotheses drawn from the model that explained the performance characteristics. To test the model, we have designed and run comprehensive experiments and conducted in-depth statistical analyses on the obtained empirical data. As a result, our model achieves about 77% amount of variance explained on the query time and indicates that reducing kernel time and data transfer time are the key factors to improve the query time. Also, our results show that the studied systems should resolve several concerns such as bounded processing within GPU memory, lack of rich query evaluation operators, limited scalability, and GPU under-utilization.

Shortest Distance Queries on Encrypted Graph Data Based on GENOA

Abstract: In the execution time of the nearest distance queries, which means shortest distance queries on the encrypted graph data that stored in external storage like cloud storage, in previous scheme there are some challenges as how to figure the accurate shortest distance in efficient and secure way. In previous work a novel scheme of Somewhat Homomorphic Encryption (SWHE) is implemented to overcome the issues mentioned above and this SWHE is used for encryption. The SWHE will encrypt the output values (shortest distance) by 2-hop cover labeling (2HCL). The storage space of this SWHE is not sufficient for the data owner and it mostly gives the negative results and very low efficient. So, to provide the best efficient, accurate result and better storage for data owner a new novel scheme is implemented in this paper. A new proposed scheme called Graph Encryption scheme for shortest distance queries (GENOA) and the 2HCL index is also implemented. This GENOA is for execution of the shortest distance queries of graph data, this proposed scheme is highly efficient, and give the accurate result as per the queries. This new scheme help in storage and security also. Index Terms: Graph encryption; 2-hop cover labelling; Graph Encryption scheme for shortest distance queries (GENOA); Shortest distance queries.

Execution Time Prediction for Cypher Queries in the Neo4j Database Using a Learning Approach

With database management systems becoming complex, predicting the execution time of graph queries before they are executed is one of the challenges for query scheduling, workload management, resource allocation, and progress monitoring. Through the comparison of query performance prediction methods, existing research works have solved such problems in traditional SQL queries, but they cannot be directly applied in Cypher queries on the Neo4j database. Additionally, most query performance prediction methods focus on measuring the relationship between correlation coefficients and retrieval performance. Inspired by machine-learning methods and graph query optimization technologies, we used the RBF neural network as a prediction model to train and predict the execution time of Cypher queries. Meanwhile, the corresponding query pattern features, graph data features, and query plan features were fused together and then used to train our prediction models. Furthermore, we also deployed a monitor node and designed a Cypher query benchmark for the database clusters to obtain the query plan information and native data store. The experimental results of four benchmarks showed that the average mean relative error of the RBF model reached 16.5% in the Northwind dataset, 12% in the FIFA2021 dataset, and 16.25% in the CORD-19 dataset. This experiment proves the effectiveness of our proposed approach on three real-world datasets.