Join Queries Research Articles

Efficient distance join query processing in distributed spatial data management systems

Due to the ubiquitous use of spatial data applications and the large amounts of such data these applications use, the processing of large-scale distance joins in distributed systems is becoming increasingly popular. Distance Join Queries (DJQs) are important and frequently used operations in numerous applications, including data mining, multimedia and spatial databases. DJQs (e.g., k Nearest Neighbor Join Query, k Closest Pair Query, ε Distance Join Query, etc.) are costly operations, since they involve both the join and distance-based search, and performing DJQs efficiently is a challenging task. Recent Big Data developments have motivated the emergence of novel technologies for distributed processing of large-scale spatial data in clusters of computers, leading to Distributed Spatial Data Management Systems (DSDMSs). Distributed cluster-based computing systems can be classified as Hadoop-based or Spark-based systems. Based on this classification, in this paper, we compare two of the most recent and leading DSDMSs, SpatialHadoop and LocationSpark, by evaluating the performance of several existing and newly proposed parallel and distributed DJQ algorithms under various settings with large spatial real-world datasets. A general conclusion arising from the execution of the distributed DJQ algorithms studied is that, while SpatialHadoop is a robust and efficient system when large spatial datasets are joined (since it is built on top of the mature Hadoop platform), LocationSpark is the clear winner in total execution time efficiency when medium spatial datasets are combined (due to in-memory processing provided by Spark). However, LocationSpark requires higher memory allocation when large spatial datasets are involved in DJQs (even more so when k and ε are large). Finally, this detailed performance study has demonstrated that the new distributed DJQ algorithms we have proposed are efficient, robust and scalable with respect to different parameters, such as dataset sizes, k, ε and number of computing nodes.

A Cost Model for SPARK SQL

In this paper, we propose a novel cost model for Spark SQL. The cost model covers the class of Generalized Projection, Selection, Join (GPSJ) queries. The cost model keeps into account the network and IO costs as well as the most relevant CPU costs. The execution cost is computed starting from a physical plan produced by Spark. The set of operations adopted by Spark when executing a GPSJ query are analytically modeled based on the cluster and application parameters, together with a set of database statistics. Experimental results carried out on three benchmarks and on two clusters of different sizes and with different computation features show that our model can estimate the actual execution time with about the 20 percent of errors on the average. Such an accuracy is good enough to let the system choose the most effective plan even when the execution time differences are limited. The error can be reduced to 14 percent, if the analytic model is coupled with our straggler handling strategy.

Join query optimization in the distributed database system using an artificial bee colony algorithm and genetic operators

SummaryAs the main factor in the distributed database systems, query optimization is aimed at finding an optimal execution plan to reduce the runtime. In such systems, because of the repeated relations on various sites, the query optimization is very challenging. Moreover, the query optimization issue with large‐scale distributed databases is an NP‐hard problem. Therefore, in this paper, an Artificial Bee Colony Algorithm based on Genetic Operators (ABC‐GO) is proposed to find a solution to join the query optimization problems in the distributed database systems. The ABC algorithm has the global–local search capabilities and genetic operators to create new candidate solutions for improving the performance of the ABC algorithm. The obtained results have shown that the cost of the query evaluation is minimized and the quality of Top‐K query plans is improved for a given distributed query. Moreover, this method decreases the overhead. However, it needs a longer execution time.

A Hardware-Accelerated Solution for Hierarchical Index-Based Merge-Join

Hardware acceleration through field programmable gate arrays (FPGAs) has recently become a technique of growing interest for many data-intensive applications. Join query is one of the most fundamental database query types useful in relational database management systems. However, the available solutions so far have been beset by higher costs in comparison to other query types. In this paper, we develop a novel solution to accelerate the processing of sort-merge join queries with low match rates. Specifically, our solution makes use of hierarchical indexes to identify result-yielding regions in the solution space in order to take advantage of result sparseness. Further, in addition to one-dimensional equi-join query processing, our solution supports processing of multidimensional similarity join queries. Experimental results show that our solution is superior to the best existing method in a low match rate setting; the method achieves a speedup factor of 4.8 for join queries with a match rate of 5 percent.

Distributed Join Query Processing for Big RDF Data

The expansion of the services of the Semantic Web and the evolution of cloud computing technologies have significantly enhanced the capability of preserving and publishing information in standard open web formats, such that data can be both human-readable and machine-processable. This situation meets the challenge in the current big data era to effectively store, retrieve, and analyze resource description framework (RDF) data in swarms. Moreover, efficient data storage and retrieval that can scale to large amounts of possibly schema-less data have proven to be quite difficult to achieve, specifically, RDF data storage with complex and large graph patterns for representing semantic data, and SPARQL query languages. In this paper, we provide comprehensive discussion about the proposed algorithms of Join.Query processing of RDF data by considering MapReduce Framework in a distributed environment. Moreover, we introduced a framework for RDF query processing and the benchmark that is used for the performance evaluation. Finally, we offer an evaluation discussion on distributed join query processing for big RDF data.

Shared Execution Approach to ε-Distance Join Queries in Dynamic Road Networks

Given a threshold distance ε and two object sets R and S in a road network, an ε-distance join query finds object pairs from R × S that are within the threshold distance ε (e.g., find passenger and taxicab pairs within a five-minute driving distance). Although this is a well-studied problem in the Euclidean space, little attention has been paid to dynamic road networks where the weights of road segments (e.g., travel times) are frequently updated and the distance between two objects is the length of the shortest path connecting them. In this work, we address the problem of ε-distance join queries in dynamic road networks by proposing an optimized ε-distance join algorithm called EDISON, the key concept of which is to cluster adjacent objects of the same type into a group, and then to optimize shared execution for the group to avoid redundant network traversal. The proposed method is intuitive and easy to implement, thereby allowing its simple integration with existing range query algorithms in road networks. We conduct an extensive experimental study using real-world roadmaps to show the efficiency and scalability of our shared execution approach.

Join query optimization techniques for complex event processing applications

Complex event processing (CEP) is a prominent technology used in many modern applications for monitoring and tracking events of interest in massive data streams. CEP engines inspect real-time information flows and attempt to detect combinations of occurrences matching predefined patterns. This is done by combining basic data items, also called "primitive events", according to a pattern detection plan, in a manner similar to the execution of multi-join queries in traditional data management systems. Despite this similarity, little work has been done on utilizing existing join optimization methods to improve the performance of CEP-based systems. In this paper, we provide the first theoretical and experimental study of the relationship between these two research areas. We formally prove that the CEP Plan Generation problem is equivalent to the Join Query Plan Generation problem for a restricted class of patterns and can be reduced to it for a considerably wider range of classes. This result implies the NP-completeness of the CEP Plan Generation problem. We further show how join query optimization techniques developed over the last decades can be adapted and utilized to provide practically efficient solutions for complex event detection. Our experiments demonstrate the superiority of these techniques over existing strategies for CEP optimization in terms of throughput, latency, and memory consumption.

IHOME: Index-Based JOIN Query Optimization for Limited Big Data Storage

Query optimization in Big Data becomes a promising research direction due to the popularity of massive data analytical systems such as Hadoop system. The query optimization is getting hard to efficiently execute JOIN queries on top of Hadoop query language, Hive, over limited Big Data storages. According to our previous work, HiveQL Optimization for JOIN query over Multi-session Environment (HOME) system has been introduced over Hadoop system to improve its performance by storing the intermediate results to avoid repeated computations. Time overheads and Big Data storages limitation are considered the main drawback of the HOME system, especially in the case of using additional physical storages or renting extra virtualized storages. In this paper, an index-based system for reusing data called indexing HiveQL Optimization for JOIN over Multi-session Big Data Environment (iHOME) is proposed to overcome HOME overheads by storing only the indexes of the joined rows instead of storing the full intermediate results directly. Moreover, the proposed iHOME system addresses eight cases of JOIN queries which classified into three groups; Similar-to-iHOME, Compute-on-iHOME, and Filter-of-iHOME. According to the experimental results of the iHOME system using TPC-H benchmark, it is found that the execution time of eight JOIN queries using iHOME on Hive has been reduced. Also, the stored data size in the iHOME system is reduced relative to the HOME system, as well as, the Big Data storage is saved. So, by increasing stored data size, the iHOME system guarantees the space scalability and overcomes the storage limitation.

Grid-Based Parallel Algorithms of Join Queries for Analyzing Multi-Dimensional Data on MapReduce

Grid-Based Parallel Algorithms of Join Queries for Analyzing Multi-Dimensional Data on MapReduce

The Impact of Database Structure on Join Query’s Energy Consumption

The Impact of Database Structure on Join Query’s Energy Consumption

Efficient large-scale distance-based join queries in spatialhadoop

Efficient processing of Distance-Based Join Queries (DBJQs) in spatial databases is of paramount importance in many application domains. The most representative and known DBJQs are the K Closest Pairs Query (KCPQ) and the e Distance Join Query (eDJQ). These types of join queries are characterized by a number of desired pairs (K) or a distance threshold (e) between the components of the pairs in the final result, over two spatial datasets. Both are expensive operations, since two spatial datasets are combined with additional constraints. Given the increasing volume of spatial data originating from multiple sources and stored in distributed servers, it is not always efficient to perform DBJQs on a centralized server. For this reason, this paper addresses the problem of computing DBJQs on big spatial datasets in SpatialHadoop, an extension of Hadoop that supports efficient processing of spatial queries in a cloud-based setting. We propose novel algorithms, based on plane-sweep, to perform efficient parallel DBJQs on large-scale spatial datasets in SpatialHadoop. We evaluate the performance of the proposed algorithms in several situations with large real-world as well as synthetic datasets. The experiments demonstrate the efficiency and scalability of our proposed methodologies.

A Taxonomy for Distance-Based Spatial Join Queries

Join operation is one of the most used operations in database management systems, including spatial databases. Hence, spatial join queries are very important in spatial database processing. There are many different kinds of spatial join queries, due to the richness in spatial data types and spatial operations. Therefore, it is important to understand the full spectrum of spatial join queries. The aim of this paper is to give a classification to one family type of spatial join, called the Distance-based Spatial Join. In the taxonomy, the authors divide this spatial join into three categories: (i) AllRange, (ii) All-kNN, and (iii) All-RNN. Each of these categories has its own variants. In this taxonomy, the authors confine the discussions to join queries on fixed points.

Distributed Top-K Join Queries Optimizing for RDF Datasets

In recent years, the scale of RDF datasets is increasing rapidly, the query research on RDF datasets in the transitional centralized environment is unable to meet the increasing demand of data query field, especially the top-k query. Based on the Spark distributed computing system and the HBase distributed storage system, a novel method is proposed for top-k query. A top–k query plan STA (Spark Threshold Algorithm) is proposed to reduce the connection operation of RDF data. Furthermore, a better algorithm SSJA (Spark Simple Join Algorithm) is presented to reduce the sorting related operations for the intermediate data. A cache mechanism is also proposed to speed up the SSJA algorithm. The experimental results show that the SSJA algorithm performs better than the STA algorithm in term of the cost and applicability, and it can significantly improve the SSJA's performance by introducing the cache mechanism.

Optimizing Join in HIVE Star Schema Using Key/Facts Indexing

ABSTRACTThese days Big Data represents complex and an important issue for the extraction/retrieval of information due to the fact that its analysis requires massive computation power. In addition, database star schema can be considered as one of the complicated data models due to the use of joining queries heavily for information extraction and reports generation, which demands scanning for a large amount of data (tera, peta, zeta bytes, etc.). On the other hand, HIVE is considered one of the essential and efficient Big Data SQL-based tools built on the top of Hadoop as a translator from SQL queries into Map/Reduce tasks. In addition, using data indexing techniques with join queries could improve /speed up HIVE join query tasks execution especially in a star schema. According to the work in this paper, Key/Facts indexing methodology was introduced to materialize the star schema and inject a simple index for data. Based on this, Key/Facts indexing methodology SQL queries’ execution time in HIVE improved without changing HIVE framework. TPC-H benchmark was used in order to estimate the performance of Key/Facts methodology. Experimental results prove that Key/Facts methodology out-performs traditional HIVE join execution time. Also, Key/Facts performance is improved by increasing the data size. Generally, Key/Facts can be considered one of the suitable methodologies for Big Data analysis.

IE2M: Design of intellectual energy efficient multicast routing protocol for ad-hoc network

In this paper, an intellectual energy efficient multicast routing protocol is proposed. It achieves enhanced performance over On-Demand Multicast Routing Protocol (ODMRP). The proposed protocol finds energy efficient multicast routes from source node to a group of receivers. Multicast mesh creation involves two phases: a Join Query (J-Q) phase and a Join Reply (J-R) phase. The J-Q phase initiates a route discovery process to find routes of the multicast group. In J-R phase, different routes of the multicast groups are set up. In the proposed protocol, we modify ODMRP and introduce fuzzy inference system to deal with imprecise and partial information during the route discovery phase. The decision maker uses two fuzzy variables such as energy and distance for evaluating reward as an output parameter of each multicast route. This output parameter helps to distinguish different multicast route and it also helps to reduce the effect of mutual interference between routes. The proposed protocol is simulated using the NS-2 simulator. The performance of the proposed protocol is compared with existing multicast routing protocols, and results outperform existing protocols in terms of several network metrics.

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

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

Exploratory product search using top-k join queries

Given a relation that contains main products and a set of relations corresponding to accessory products that can be combined with a main product, the Exploratory Top-k Join query retrieves the k best combinations of main and accessory products based on user preferences. As a result, the user is presented with a set of k combinations of distinct main products, where a main product is combined with accessory products only if the combination has a better score than the single main product. We model this problem as a rank-join problem, where each combination is represented by a tuple from the main relation and a set of tuples from (some of) the accessory relations. The nature of the problem is challenging because the inclusion of accessory products is not predefined by the user, but instead all potential combinations (joins) are explored during query processing in order to identify the highest scoring combinations. Existing approaches cannot be directly applied to this problem, as they are designed for joining a predefined set of relations. In this paper, we present algorithms for processing exploratory top-k joins that adopt the pull-bound framework for rank-join processing. We introduce a novel algorithm (XRJN) which employs a more efficient bounding scheme and allows earlier termination of query processing. We also provide theoretical guarantees on the performance of this algorithm, by proving that XRJN is instance-optimal. In addition, we consider a pulling strategy that boosts the performance of query processing even further. Finally, we conduct a detailed experimental study that demonstrates the efficiency of the proposed algorithms in various setups.

분산 컴퓨팅 환경에서 효율적인 유사 조인 질의 처리를 위한 행렬 기반 필터링 및 부하 분산 알고리즘

분산 컴퓨팅 환경에서 효율적인 유사 조인 질의 처리를 위한 행렬 기반 필터링 및 부하 분산 알고리즘

New plane-sweep algorithms for distance-based join queries in spatial databases

Efficient and effective processing of the distance-based join query (DJQ) is of great importance in spatial databases due to the wide area of applications that may address such queries (mapping, urban planning, transportation planning, resource management, etc.). The most representative and studied DJQs are the K Closest Pairs Query (KCPQ) and εDistance Join Query (εDJQ). These spatial queries involve two spatial data sets and a distance function to measure the degree of closeness, along with a given number of pairs in the final result (K) or a distance threshold (ε). In this paper, we propose four new plane-sweep-based algorithms for KCPQs and their extensions for εDJQs in the context of spatial databases, without the use of an index for any of the two disk-resident data sets (since, building and using indexes is not always in favor of processing performance). They employ a combination of plane-sweep algorithms and space partitioning techniques to join the data sets. Finally, we present results of an extensive experimental study, that compares the efficiency and effectiveness of the proposed algorithms for KCPQs and εDJQs. This performance study, conducted on medium and big spatial data sets (real and synthetic) validates that the proposed plane-sweep-based algorithms are very promising in terms of both efficient and effective measures, when neither inputs are indexed. Moreover, the best of the new algorithms is experimentally compared to the best algorithm that is based on the R-tree (a widely accepted access method), for KCPQs and εDJQs, using the same data sets. This comparison shows that the new algorithms outperform R-tree based algorithms, in most cases.

Learning Join Queries from User Examples

We investigate the problem of learning join queries from user examples. The user is presented with a set of candidate tuples and is asked to label them as positive or negative examples, depending on whether or not she would like the tuples as part of the join result. The goal is to quickly infer an arbitrary n -ary join predicate across an arbitrary number m of relations while keeping the number of user interactions as minimal as possible. We assume no prior knowledge of the integrity constraints across the involved relations. Inferring the join predicate across multiple relations when the referential constraints are unknown may occur in several applications, such as data integration, reverse engineering of database queries, and schema inference. In such scenarios, the number of tuples involved in the join is typically large. We introduce a set of strategies that let us inspect the search space and aggressively prune what we call uninformative tuples, and we directly present to the user the informative ones—that is, those that allow the user to quickly find the goal query she has in mind. In this article, we focus on the inference of joins with equality predicates and also allow disjunctive join predicates and projection in the queries. We precisely characterize the frontier between tractability and intractability for the following problems of interest in these settings: consistency checking, learnability, and deciding the informativeness of a tuple. Next, we propose several strategies for presenting tuples to the user in a given order that allows minimization of the number of interactions. We show the efficiency of our approach through an experimental study on both benchmark and synthetic datasets.