Multi-join Query Research Articles

Analysis of Multi-Join Query Optimization Using ACO and Q-Learning

Analysis of Multi-Join Query Optimization Using ACO and Q-Learning

Query Join Order Optimization Method Based on Dynamic Double Deep Q-Network

A join order directly affects database query performance and computational overhead. Deep reinforcement learning (DRL) can explore efficient query plans while not exhausting the search space. However, the deep Q network (DQN) suffers from the overestimation of action values in query optimization, which can lead to limited query performance. In addition, ε-greedy exploration is not efficient enough and does not enable deep exploration. Accordingly, in this paper, we propose a dynamic double DQN (DDQN) order selection method(DDOS) for join order optimization. First, the method models the join query as a Markov decision process (MDP), then solves the DRL model by integrating the network model DQN and DDQN weighting into the DRL model’s estimation error problem in query joining, and finally improves the quality of developing query plans. And actions are selected using a dynamic progressive search strategy to improve the randomness and depth of exploration and accumulate a high information gain of exploration. The performance of the proposed method is compared with those of dynamic programming, heuristic algorithms, and DRL optimization methods based on the query set Join Order Benchmark (JOB). The experimental results show that the proposed method can effectively improve the query performance with a favorable generalization ability and robustness, and outperforms other baselines in multi-join query applications.

Optimization Method for Distributed Database Query Based on an Adaptive Double Entropy Genetic Algorithm

In a distributed database environment, multi-join query optimization is one of the key factors affecting database performance. Genetic algorithms have a good application in dealing with this type of problem. However, the traditional genetic algorithm has the problems of low efficiency and easily falls into the precocity when dealing with query optimization, which is mainly caused by the lack of population diversity. Therefore, this paper sets up a mathematical model for distributed database query optimization and proposes an adaptive genetic algorithm based on double entropy. We introduced a genetic algorithm with two types of entropy: genotype and phenotype. Genotype entropy was used to optimize the distribution of the initial population, ensuring that the initial population has good population diversity. Phenotype entropy is used to optimize the genetic strategy, which can be divided into individual entropy and population entropy. Individual entropy is used to optimize the selection strategy, and population entropy is used to optimize the crossover and mutation operators to maintain the population diversity in the iteration process and accelerate the speed of iteration. The experimental results show that the algorithm proposed in this paper is effective for query optimization of a distributed database.

Multi-objective Multi-Join Query Optimization using Modified Grey Wolf Optimization

Nowadays information retrieved by a query is based upon extracting data across the world, which are located in different data sites. In distributed database management systems (DDBMS), due to partitioning or replication of data among several sites the relations required for an answer of a query may be stored at several data sites (DS). Many experimental results have showed that combination of optimal join order (OJO) and optimal selection of relations in query plan (QP) gives out better results compare to the several existing query optimising methodologies like teacher-learner based optimisation (TLBO), genetic algorithm (GA), etc. In this paper an approach has been proposed to compute a best optimal QP that could answer the user query with minimal cost values and minimum time using modified grey wolf optimisation algorithm (MGWO) which is multi-objective constrained. Proposed approach also aims for producing OJO in order to reduce the dimensionality complexity of the QP.

Efficient Processing of Queries using Filtered Bitmap Index with Multi-Join Multiple Set Predicates

Efficient query process is an essential task in numerous environments that relate large sum of information. The performance degradation occurs when the sum of information is increased. It will further degrade when the amount of joins in the queries is increased. These problems emphasize a need for good query processing approach. Thus, in this report, we take a various method to optimize the multi-join query with multiple set predicates in Data warehousing environment. So we have proposed an effective algorithm as Filtered Bitmap Index with multi-join multiple set predicates processing approach and examine the time complexity on huge data set with multiple tables. In this approach, the multi-join query is processed by selecting the tabular array based on their level number from lower to higher. A simple rewritten query was created from the given complex query exploitation uses the lowest level table and executed. If the result exists then only continue the join processing in the rewritten query, by taking the next lower level table from the complex query and do the execution. The ratio of our technique is to demonstrated with moving experiment using WorldCup98 and TPC-H benchmark datasets

Multi-join query optimization in bucket-based encrypted databases using an enhanced ant colony optimization algorithm

One of the organizations' main concerns is to protect sensitive data in database systems, especially the ones outsourced to untrusted service providers. An effective solution for this issue is to e...

An Optimization for Distributed Database Multi-join Query Based on Improved Genetic Algorithm

The paper considers the optimization of the multi-join query process of distributed database, which is mainly based on the computer time or resource cost and search strategy of querying database. To model the cost, the multi-join query is divided from a full-join query into some semi-join queries steps to reduce the communication cost and then a new cost model is presented. As for the search strategy, by comparing the advantages and disadvantages of dynamic programming algorithm (DPA), simulated annealing algorithm (SAA) and iterative correction algorithm (ICA), genetic algorithm (GA) is improved to optimize the query process because of its better search performance. To implement such an optimization, the paper deals with GA’s process of coding, selection, crossover and mutation carefully in novel way so as to improve the accuracy and speed of searching for optimal values of querying a distributed data base. Finally, the simulation results show that the optimization of cost model and search strategy achieves the expected effect and it can improve the accuracy and speed of the distributed database query.

Improved Multi Join Query Optimization for RDBMS Based on Swarm Intelligence Approaches

Improved Multi Join Query Optimization for RDBMS Based on Swarm Intelligence Approaches

An End-Around Approach for Efficient Join Query Processing

This paper introduced a method for producing immediate and result in multi-join query, in homogeneous and heterogeneous environment. In recent years Adaptive or Non Blocking join algorithms have attracted a lot of attention in streaming applications, where data is provided from autonomous data sources in heterogeneous network environments. This algorithms are better as compared to traditional algorithms is that they can generates join results as early as the first input tuples are on hand hence it improves pipelining, smooth out join result production and also masking source or network delays. As response time of the queries places a very important role in adaptive join, the join algorithm like Hash Join, Sort Merge Join are become unacceptable for this environment because they require preprocessing before generating the join result. Hence, in adaptive join technique only possible algorithm is Nested loop join. In Nested Loop Join, every single record of the outer relation is compared with every single record of the inner relation. The no. of comparisons done by the nested loop join can be reduced by making improvement in Block Nested loop Join. In proposed End-Around Block Nested loop join outer and inner table’s comparison is done in parallel and whenever a row in first location didn’t find a match then row from first location removed and placed at rear end as like in a queue, the matched row removed from inner relation and added to result set. Whenever, New tuple arrive is then pushed into rear end and process is continuing with new incoming tuples in streaming environments.

Optimizing Communication for Multi-Join Query Processing in Cloud Data Warehouses

In this paper, the authors present storage structures, PK-map and Tuple-index-map, to improve the performance of query execution and inter-node communication in Cloud Data Warehouses. Cloud Data Warehouses require Read-Optimized databases because large amount of historical data are integrated on a regular basis to facilitate analytical applications for report generation, future analysis, and decision-making. This frequent data integration can grow the data size rapidly and hence there is a need to allocate resource dynamically on demand. As resource is scaled-out in the cloud environment, the number of nodes involved in the execution of a query increases. This in turn increases the number of inter-node communications. In queries, join operation between two different tables are most common. To perform the join operation of a query in the cloud environment, data need to be transferred among different nodes. This becomes critical when there is a huge amount of data (in Terabytes or Petabytes) stored across a large number of nodes. With the increase in number of nodes and amount of data, the size of the communication messages also increases, resulting in even increased bandwidth usage and performance degradation. In this paper, the authors show through extensive experiments using PlanetLab Cloud that their proposed storage structures PK-map and Tuple-index-map, and query execution algorithms improve the performance of join queries, decrease inter-node communication and workload in Cloud Data Warehouses.

On Producing High and Early Result Throughput in Multijoin Query Plans

This paper introduces an efficient framework for producing high and early result throughput in multijoin query plans. While most previous research focuses on optimizing for cases involving a single join operator, this work takes a radical step by addressing query plans with multiple join operators. The proposed framework consists of two main methods, a flush algorithm and operator state manager. The framework assumes a symmetric hash join, a common method for producing early results, when processing incoming data. In this way, our methods can be applied to a group of previous join operators (optimized for single-join queries) when taking part in multijoin query plans. Specifically, our framework can be applied by 1) employing a new flushing policy to write in-memory data to disk, once memory allotment is exhausted, in a way that helps increase the probability of producing early result throughput in multijoin queries, and 2) employing a state manager that adaptively switches operators in the plan between joining in-memory data and disk-resident data in order to positively affect the early result throughput. Extensive experimental results show that the proposed methods outperform the state-of-the-art join operators optimized for both single and multijoin query plans.

Performance improvement of join queries through algebraic signatures

Evaluation of distributed join queries in large scale environments often deals with large data volumes, network with low bandwidth and high number of data sources. Several techniques were proposed to improve join query performances. We propose to combine algebraic signatures with known hash join techniques. Algebraic signatures combined to a semi join based join technique are also very useful for reducing the volume of inter-site transmitted data. We discuss impacts of CPU, I/O and communication costs reductions on the total multi-join query performance improvements. The performance study of our technique shows the reduction of both memory requirements and processing costs especially when the join attribute is a long string. Performances of the semi join based join technique are also significantly improved. We experiment with simple, medium and complex queries in both centralised and distributed environments. Algebraic signatures provide improved performances to reduce significantly communication costs, often the dominant factor in distributed environments.

Distributed multi-join query processing in data grids

Query processing in data grids is a difficult issue due to the heterogeneous, unpredictable and volatile behaviors of the grid resources. Applying join operations on remote relations in data grids is a unique and interesting problem. However, to the best of our knowledge, little is done to date on multi-join query processing in data grids. An approach for processing multi-join queries is proposed in this paper. Firstly, a relation–reduction algorithm for reducing the sizes of operand relations is presented in order to minimize data transmission cost among grid nodes. Then, a method for scheduling computer nodes in data grids is devised to parallel process multi-join queries. Thirdly, an innovative method is developed to efficiently execute join operations in a pipeline fashion. Finally, a complete algorithm for processing multi-join queries is given. Analytical and experimental results show the effectiveness and efficiency of the proposed approach.

Join and multi-join processing in data integration systems

Query processing in a data integration system is complicated by a lack of quality statistics about the data, unpredictable and bursty data transfer rates, and slow or unavailable data sources. Conventional query processing algorithms, which are based on a blocking execution model, are no longer attractive because of their long initial response time. Moreover, the execution engine may be stalled by slow data delivery rates or unavailable data sources. In this paper, we adopt a non-blocking execution model for evaluating queries. We propose a symmetric partition-based join algorithm, called AJoin, that can operate with small memory requirement, produce first few answer tuples quickly, and blocks only when all available data have been examined. We also examine heuristics to manage the partitions and address the memory management issues of AJoin. To evaluate multi-join query plans, we also proposed two new strategies, m-AJoin and Pm-AJoin. Both strategies evaluate each join operation using AJoin. While m-AJoin accesses data from remote sources in its entirety, Pm-AJoin accesses remote data in chunks of smaller partitions. Our performance study shows the effectiveness of the proposed approaches for join and multi-join processing in a multi-user data integration system.

Optimizing equijoin queries in distributed databases where relations are hash partitioned

Consider the class of distributed database systems consisting of a set of nodes connected by a high bandwidth network. Each node consists of a processor, a random access memory, and a slower but much larger memory such as a disk. There is no shared memory among the nodes. The data are horizontally partitioned often using a hash function. Such a description characterizes many parallel or distributed database systems that have recently been proposed, both commercial and academic. We study the optimization problem that arises when the query processor must repartition the relations and intermediate results participating in a multijoin query. Using estimates of the sizes of intermediate relations, we show (1) optimum solutions for closed chain queries; (2) the NP-completeness of the optimization problem for star, tree, and general graph queries; and (3) effective heuristics for these hard cases. Our general approach and many of our results extend to other attribute partitioning schemes, for example, sort-partitioning on attributes, and to partitioned object databases.