Query Optimization Framework Research Articles

LEON: A New Framework for ML-Aided Query Optimization

Query optimization has long been a fundamental yet challenging topic in the database field. With the prosperity of machine learning (ML), some recent works have shown the advantages of reinforcement learning (RL) based learned query optimizer. However, they suffer from fundamental limitations due to the data-driven nature of ML. Motivated by the ML characteristics and database maturity, we propose LEON -a framework for ML-aidEd query OptimizatioN. LEON improves the expert query optimizer to self-adjust to the particular deployment by leveraging ML and the fundamental knowledge in the expert query optimizer. To train the ML model, a pairwise ranking objective is proposed, which is substantially different from the previous regression objective. To help the optimizer to escape the local minima and avoid failure, a ranking and uncertainty-based exploration strategy is proposed, which discovers the valuable plans to aid the optimizer. Furthermore, an ML model-guided pruning is proposed to increase the planning efficiency without hurting too much performance. Extensive experiments offer evidence that the proposed framework can outperform the state-of-the-art methods in terms of end-to-end latency performance, training efficiency, and stability.

Query Optimization Framework for Graph Database in Cloud Dew Environment

The query optimizer uses cost-based optimization to create an execution plan with the least cost, which also consumes the least amount of resources. The challenge of query optimization for relational database systems is a combinatorial optimization problem, which renders exhaustive search impossible as query sizes rise. Increases in CPU performance have surpassed main memory, and disk access speeds in recent decades, allowing data compression to be used—strategies for improving database performance systems. For performance enhancement, compression and query optimization are the two most factors. Compression reduces the volume of data, whereas query optimization minimizes execution time. Compressing the database reduces memory requirement, data takes less time to load into memory, fewer buffer missing occur, and the size of intermediate results is more diminutive. This paper performed query optimization on the graph database in a cloud dew environment by considering, which requires less time to execute a query. The factors compression and query optimization improve the performance of the databases. This research compares the performance of MySQL and Neo4j databases in terms of memory usage and execution time running on cloud dew servers.

A Framework for IoT Streaming Data Indexing and Query Optimization

Streaming data are continuously generated by multiple Internet of Things (IoT) sources, e.g., sensors, mobile devices, etc., and sent simultaneously to relevant applications to be processed in real time in a continuous and timely fashion. Existing research has dealt with the integration of IoT streaming data from multiple sources. Some of the earlier research introduced models to facilitate streaming data compression and the subsequent indexing. However, as IoT data come from multiple sensors and differ in terms of variety and velocity, there is still an urgent need to build an efficient indexing mechanism so that we can optimise responses to users’ queries. In this research, we identify a variety of queries from different motivating scenarios and develop a framework to optimise the way to access and retrieve IoT streaming data to respond to the users’ queries. To this end, (i) a streaming data scenario is analysed, and (ii) an optimisation framework with indexing schemes from multiple sources are introduced, and (iii) some common and illustrative queries are presented to explain optimisation. Finally, a set of experiments on five queries is performed to illustrate the ability of the optimisation framework in a wide range of different scenarios. The results prove that the proposed optimisation framework is much better than the existing IoT data integration and indexing frameworks.

Designing an open framework for query optimization and compilation

Since its invention, data-centric code generation has been adopted for query compilation by various database systems in academia and industry. These database systems are fast but maximize performance at the expense of developer friendliness, flexibility, and extensibility. Recent advances in the field of compiler construction identified similar issues for domain-specific compilers and introduced a solution with MLIR, a generic infrastructure for domain-specific dialects. We propose a layered query compilation stack based on MLIR with open intermediate representations that can be combined at each layer. We further propose moving query optimization into the query compiler to benefit from the existing optimization infrastructure and make cross-domain optimization viable. With LingoDB, we demonstrate that the used approach significantly decreases the implementation effort and is highly flexible and extensible. At the same time, LingoDB achieves high performance and low compilation latencies.

A cost model for random access queries in document stores

Document stores have become one of the key NoSQL storage solutions. They have been widely adopted in different domains due to their ability to store semi-structured data and expressive query capabilities. However, implementations differ in terms of concrete data storage and retrieval. Unfortunately, a standard framework for data and query optimization for document stores is nonexistent, and only implementation-specific design and query guidelines are used. Hence, the goal of this work is to aid automating the data design for document stores based on query costs instead of generic design rules. For this, we define a generic storage and query cost model based on disk access and memory allocation that allows estimating the impact of design decisions. Since all document stores carry out data operations in memory, we first estimate the memory usage by considering characteristics of the stored documents, their access patterns, and memory management algorithms. Then, using this estimation and metadata storage size, we introduce a cost model for random access queries. We validate our work on two well-known document store implementations: MongoDB and Couchbase. The results show that the memory usage estimates have the average precision of 91% and predicted costs are highly correlated to the actual execution times. During this work, we have managed to suggest several improvements to document storage systems. Thus, this cost model also contributes to identifying discordance between document store implementations and their theoretical expectations.

An Optimal Framework for Spatial Query Optimization Using Hadoop in Big Data Analytics

Background and Objective: Spatial queries frequently used in Hadoop for significant data process. However, vast and massive size of spatial information makes it difficult to process the spatial inquiries proficiently, so they utilized the Hadoop system for process the Big Data. Boolean Queries & Geometry Boolean Spatial Data for Query Optimization using Hadoop System are used. In this paper, a lightweight and adaptable spatial data index for big data have discussed, which have used to process in Hadoop frameworks. Results demonstrate the proficiency and adequacy of spatial ordering system for various spatial inquiries. Methods: In this section, the different type of approaches are used which helps to understand the procedure to develop an efficient system by involving the methods like efficient and scalable method for processing Top-k spatial Boolean Queries, Efficient query processing in Geographic web search engines. Geographic search engine query processing combines text and spatial data processing technique & Top-k spatial preference Queries. In this work, the implementation of all the methods is done for comparative analysis. Results and Discussion: The execution of algorithm gives results which show the difference of performance over different data types. Three different graphs are presented here based on the different data inputs indexing and data types. Results show that when the number of rows to be executed increases the performance of geohash decreases, while the crucial point for change in performance of execution is not visible due to sudden hike in number of rows returned. Conclusion: The query processing have discussed in geographic web search engines. In this work a general framework for ranking search results based on a combination of textual and spatial criteria, and proposed several algorithms for efficiently executing ranked queries on very large collections have discussed. The integrated of proposed algorithms into an existing high-performance search engine query processor and works on evaluating them on a large data set and realistic geographic queries. The results shows that in many cases geographic query processing can be performed at about the same level of efficiency as text-only queries.

Reinforcement-Learning-Based Query Optimization in Differentially Private IoT Data Publishing

With the advancement of Internet of Things (IoT) and computing paradigms, massive data are collected and processed to enhance intelligent applications. However, by deliberately sending some queries, an attacker may be able to derive the sensitive information of IoT data owners. To prevent privacy leakage during IoT data query, differential privacy (DP) hides private information by introducing noise to the query results. As DP introduces randomized noise that will affect query accuracy (data utility), the tradeoff between privacy preservation and data utility is a challenge. In this article, we first propose a novel optimization framework for single query to minimize the privacy cost, while satisfying both personalized DP and customized data utility. We design a reinforcement learning-based algorithm for single query optimization framework (SQOF_RL) to solve the optimization problem efficiently. Then, we propose a SQOF_RL and SVT-based batch query optimization mechanism (S2BQOM) to answer more queries privately. The performance evaluation shows that SQOF_RL and S <sup xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">2</sup> BQOM can effectively optimize single query and batch queries in terms of privacy cost, data utility, personalized privacy, and query satisfaction. Finally, the performance analysis reveals that our work can be applied to multiple linear/nonlinear query functions instead of one particular query function.

FQ-BF: An Efficient Forwarding and Query Optimization Framework based on Bloom Filters in NDN

With the increase in internet users and data generation resources; Named Data Networking (NDN), a content-driven communication model that has come into the limelight. Most of the applications in today’s world are data-intensive, prefer a data-based network over the address-based network for communication. Continuous efforts by researchers try to optimize the NDN performance in various areas like routing, forwarding, caching, security, etc. NDN basically works on searching for the required data packet, so the content search is one of the promising areas in NDN that still needs improvement. This paper proposes a novel content search algorithm based on Bloom Filters (BF) named FQ-BF; which focuses on efficient and smart forwarding of I_pkt and reduces the number of queries to search a packet in the network. The important metrics where the proposed approach has shown significant improvement as compared to standard NDN approach; include the average delay of packets, improving the packet serving capacity of the network.

Cloud-based query evaluation for energy-efficient mobile sensing

In this paper, we reduce the energy overheads of continuous mobile sensing, specifically for the case of context-aware applications that are interested in collective context or events, i.e., events expressed as a set of complex predicates over sensor data from multiple smartphones. We propose a cloud-based query management and optimization framework, called CloQue, that can support thousands of such concurrent queries, executing over a large number of individual smartphones. Our central insight is that the context of different individuals & groups often have significant correlation, and that this correlation can be learned through standard association rule mining on historical data. CloQue’s exploits such correlation to reduce energy overheads via two key innovations: (i) dynamically reordering the order of predicate processing to preferentially select predicates with not just lower sensing cost and higher selectivity, but that maximally reduce the uncertainty about other context predicates; and (ii) intelligently propagating the query evaluation results to dynamically update the confidence values of other correlated context predicates. We present techniques for probabilistic processing of context queries (to save significant energy at the cost of a query fidelity loss) and for query partitioning (to scale CloQue to a large number of users while meeting latency bounds). An evaluation, using real cellphone traces from two different datasets, shows significant energy savings (between 30% and 50% compared with traditional short-circuit systems) with little loss in accuracy (5% at most). In addition, we utilize parallel evaluation to reduce overall latency. The experiments show our approaches save up to 70% latency.

Teaching an RDBMS about ontological constraints

In the presence of an ontology, query answers must reflect not only data explicitly present in the database, but also implicit data, which holds due to the ontology, even though it is not present in the database. A large and useful set of ontology languages enjoys FOL reducibility of query answering : answering a query can be reduced to evaluating a certain first-order logic (FOL) formula (obtained from the query and ontology) against only the explicit facts. We present a novel query optimization framework for ontology-based data access settings enjoying FOL reducibility . Our framework is based on searching within a set of alternative equivalent FOL queries, i.e., FOL reformulations, one with minimal evaluation cost when evaluated through a relational database system. We apply this framework to the DL-Lite R Description Logic underpinning the W3C's OWL2 QL ontology language, and demonstrate through experiments its performance benefits when two leading SQL systems, one open-source and one commercial, are used for evaluating the FOL query reformulations.

Improving the performance of Hadoop Hive by sharing scan and computation tasks

MapReduce is a popular programming model for executing time-consuming analytical queries as a batch of tasks on large scale data clusters. In environments where multiple queries with similar selection predicates, common tables, and join tasks arrive simultaneously, many opportunities can arise for sharing scan and/or join computation tasks. Executing common tasks only once can remarkably reduce the total execution time of a batch of queries. In this study, we propose a Multiple Query Optimization framework, SharedHive, to improve the overall performance of Hadoop Hive, an open source SQL-based data warehouse using MapReduce. SharedHive transforms a set of correlated HiveQL queries into a new set of insert queries that will produce all of the required outputs within a shorter execution time. It is experimentally shown that SharedHive achieves significant reductions in total execution times of TPC-H queries.

Flexible and extensible preference evaluation in database systems

Personalized database systems give users answers tailored to their personal preferences. While numerous preference evaluation methods for databases have been proposed (e.g., skyline, top-k, k-dominance, k-frequency), the implementation of these methods at the core of a database system is a double-edged sword. Core implementation provides efficient query processing for arbitrary database queries, however, this approach is not practical since each existing (and future) preference method requires implementation within the database engine. To solve this problem, this article introduces FlexPref, a framework for extensible preference evaluation in database systems. FlexPref, implemented in the query processor, aims to support a wide array of preference evaluation methods in a single extensible code base. Integration with FlexPref is simple, involving the registration of only three functions that capture the essence of the preference method. Once integrated, the preference method “lives” at the core of the database, enabling the efficient execution of preference queries involving common database operations. This article also provides a query optimization framework for FlexPref, as well as a theoretical framework that defines the properties a preference method must exhibit to be implemented in FlexPref. To demonstrate the extensibility of FlexPref, this article also provides case studies detailing the implementation of seven state-of-the-art preference evaluation methods within FlexPref. We also experimentally study the strengths and weaknesses of an implementation of FlexPref in PostgreSQL over a range of single-table and multitable preference queries.

Flexible and extensible preference evaluation in database systems

Personalized database systems give users answers tailored to their personal preferences. While numerous preference evaluation methods for databases have been proposed (e.g., skyline, top-k, k-dominance, k-frequency), the implementation of these methods at the core of a database system is a double-edged sword. Core implementation provides efficient query processing for arbitrary database queries, however, this approach is not practical since each existing (and future) preference method requires implementation within the database engine. To solve this problem, this article introduces FlexPref, a framework for extensible preference evaluation in database systems. FlexPref, implemented in the query processor, aims to support a wide array of preference evaluation methods in a single extensible code base. Integration with FlexPref is simple, involving the registration of only three functions that capture the essence of the preference method. Once integrated, the preference method “lives” at the core of the database, enabling the efficient execution of preference queries involving common database operations. This article also provides a query optimization framework for FlexPref, as well as a theoretical framework that defines the properties a preference method must exhibit to be implemented in FlexPref. To demonstrate the extensibility of FlexPref, this article also provides case studies detailing the implementation of seven state-of-the-art preference evaluation methods within FlexPref. We also experimentally study the strengths and weaknesses of an implementation of FlexPref in PostgreSQL over a range of single-table and multitable preference queries.

Semantic query optimization in the presence of types

Both semantic and type-based query optimization rely on the idea that queries may exhibit non-trivial rewritings if the state space of the database is restricted. While these two problems have always been studied as separate problems in previous work, in this paper we present a unifying, logic-based query optimization framework that builds upon the classical chase algorithm and brings both problems together. As a major challenge, our novel setting requires chasing conjunctive queries with union and negation in the presence of dependencies containing negation and disjunction. Tackling this problem, we study the applicability of the chase in this setting, develop novel conditions that guarantee its termination, identify fragments for which minimal query computation (w.r.t. a generic cost function) is always possible, and investigate the complexity of related decision problems.

A Study on Optimization of Top-k Queries in Relational Databases

Efficient execution of top-k queries is increasingly becoming a major challenge for relational database technology. Top-k query has emerged as a key requirement in modern application. In these applications, efficient and adaptive evaluation of top-k queries is an integral part of application semantics. In this paper we discuss a query optimization framework of top-k queries that fully integrates rank-join operators. Ranking queries produce results that are ordered on some computed score. A key property of top-k queries is that, users are interested only in the first k results and not in total ranking of all query results. This property directly impacts the optimization of top-k queries by optimizing for the first k results. Traditionally, most real world database systems offer the feature of first n row optimization. The top-k query is define as- Given a database D of m objects, each of which is characterized by n attributes, a scoring function f, according to which we rank the objects in D, and the number of expected results k. Then a top-k query Q returns the k objects with the highest rank in f. In Top-k query, query define on n attribute a1, a2 ,…, an and relation M in the form of R1 ,R2 ,…,RM that each ai (i=1:n) belongs to one relation Rj (j=1:M). Each of the attributes has special domain in comparison with their kind. According to the query, a series of attributes of these relations are applied for projection, a series of attributes of these relations are used for restriction and join. In the rank aware queries there is apart for ranking that some of relations attribute are presented in the form of a ranking relation which is called ranking function. Ranking function f is formed in the form of attribute n' that is n' <=n. A theory for ranking function f is this: ranking function changes in comparison with all relations are monotonic. In addition to this, the number of suitable answers in rank aware queries is determined too that is just Top k. Consider a set of relations R1 to Rm. Each tuple in Ri is associated with some

PET

Energy conservation is a growing important issue in designing modern database management system (DBMS). This requires a deep thinking about the tradeoffs between energy and performance. Despite the significant amount of efforts at the hardware level to make the major components consume less energy, we argue for a revisit of the DBMS query processing mechanism to identify and harvest the potential of energy saving. However, the state-of-art architecture of DBMS does not take energy usage into consideration in its design. A major challenge in developing an energy-aware DBMS is to design and implement a cost-based query optimizer that evaluates query plans by both performance and energy costs. By following such a strategy, our previous work revealed the fact that energy-efficient query plans do not necessarily have the shortest processing time. This demo proposal introduces PET -- an energy-aware query optimization framework that is built as a part of the PostgreSQL kernel. PET, via its power cost estimation module and plan evaluation model, enables the database system to run under a DBA-specified energy/performance tradeoff level. PET contains a power cost estimator that can accurately estimate the power cost of query plans at compile time, and a query evaluation engine that the DBA could configure key PET parameters towards the desired tradeoff. The software to be demonstrated will also include workload engine for producing large quantities of queries and data sets. Our demonstration will show how PET functions via a comprehensive set of views from its graphical user interface named PET Viewer . Through such interfaces, a user can achieve a good understanding of the energy-related query optimization and cost-based plan generation. Users are also allowed to interact with PET to experience the different energy/performance tradeoffs by changing PET and workload parameters at query runtime.

A Query Optimization Framework for Fuzzy Relational Databases

Ever since the development of relational model, relational database systems have been extensively studied and several commercial relational database systems are currently available. Relational model usually take care of only well defined data. In order to capture more meaning to the data an extension of the classical relational model called fuzzy relational model was proposed. The key reasons for the success of relational database lies in the power of declarative languages and execution strategies used in query optimization. Estimating the cost of fuzzy query based on system catalog introduces error due to approximation involved and insufficient information at query execution time. So there is need for a query optimization framework that addresses the issues of query execution in fuzzy relational databases. This paper deals with a framework for building fuzzy cost model to obtain a good execution strategy for a query.

A Multi-Staged Blackboard Query Optimization Framework for World-Spanning Distributed Database Resources

With the advent of distributed computing, particularly since the emergence of Grids, Clouds and other Service Oriented Computing paradigms, the querying of huge datasets of distributed databases or data repositories on a global scale has become a challenging research question. Currently, beside various other topics, two major concerns in this research area have to be addressed: data access & integration and query execution planning. Our research effort addresses the second issue, namely the query optimization of distributed database queries. Hereby we consider a variety of different heterogeneous and homogeneous infrastructures, parallel algorithms, and huge datasets, which span across several virtual organizations (VOs) with usually no centralized authority. This paper introduces a novel heuristic framework for the optimization of query execution plans (QEP) on a world-wide scale. Our work is based on a multi-staged blackboard mechanism to determine which available data, resources and operations have to be considered to perform a query optimally. Moreover, an evaluation scenario proves our findings that even small changes in the selection of e.g. sort operations for a query execution tree (QET) lead to significant performance improvements.

Top- k query evaluation in sensor networks under query response time constraint

Top- k query in a wireless sensor network is to find the k sensor nodes with the highest sensing values. To evaluate the top- k query in such an energy-constrained network poses great challenges, due to the unique characteristics imposed on its sensors. Existing solutions for top- k query in the literature mainly focused on energy efficiency but little attention has been paid to the query response time and its effect on the network lifetime. In this paper we address the query response time and its effect on the network lifetime through the study of the top- k query problem in sensor networks with the response time constraint. We aim at finding an energy-efficient routing tree and evaluating top- k queries on the tree such that the network lifetime is significantly prolonged, provided that the query response time constraint is met too. To do so, we first present a cost model of energy consumption for answering top- k queries and introduce the query response time definition. We then propose a novel joint query optimization framework, which consists of finding a routing tree in the network and devising a filter-based evaluation algorithm for top- k query evaluation on the tree. We finally conduct extensive experiments by simulation to evaluate the performance of the proposed algorithms, in terms of the total energy consumption, the maximum energy consumption among nodes, the query response time, and the network lifetime. The experimental results showed that there is a non-trivial tradeoff between the query response time and the network lifetime, and the joint query optimization framework can prolong the network lifetime significantly under a specified query response time constraint.

XPath whole query optimization

Previous work reports about SXSI, a fast XPath engine which executes tree automata over compressed XML indexes. Here, reasons are investigated why SXSI is so fast. It is shown that tree automata can be used as a general framework for fine grained XML query optimization. We define the "relevant nodes" of a query as those nodes that a minimal automaton must touch in order to answer the query. This notion allows to skip many subtrees during execution, and, with the help of particular tree indexes, even allows to skip internal nodes of the tree. We efficiently approximate runs over relevant nodes by means of on-the-fly removal of alternation and non-determinism of (alternating) tree automata. We also introduce many implementation techniques which allows us to efficiently evaluate tree automata, even in the absence of special indexes. Through extensive experiments, we demonstrate the impact of the different optimization techniques.