Search

In distributed and cloud database system, large amount of resources are used by complex queries that increase in payment overheads of cloud users. These resource needs can be minimised by evaluating common join operations and caching their results so that they could be applied to the execution of additional queries. In this research, the Query Optimization Technique Using Materialization (QOTUM) system is designed and developed that uses the mechanism of materialized views during query execution at distributed database in cloud environment. Extensive experiments are conducted with the help of standard benchmarks datasets and query workloads in cloud environment. The performance of QOTUM system is studied and evaluated against conventional SQL system based on various performance parameters. [ABSTRACT FROM AUTHOR]

In distributed and cloud database system, large amount of resources are used by complex queries that increase in payment overheads of cloud users. These resource needs can be minimised by evaluating common join operations and caching their results so that they could be applied to the execution of additional queries. In this research, the Query Optimization Technique Using Materialization (QOTUM) system is designed and developed that uses the mechanism of materialized views during query execution at distributed database in cloud environment. Extensive experiments are conducted with the help of standard benchmarks datasets and query workloads in cloud environment. The performance of QOTUM system is studied and evaluated against conventional SQL system based on various performance parameters.

The increasing demand for the simultaneous transaction and review of the data for either decision making or forecasting has created a need for faster and better Hybrid Transactional/Analytical Processing (HTAP). This paper emphasizes the speedup of Online Analytical Processing (OLAP) operations in an HTAP environment where analytical queries are mainly repetitive and contain non-indexed keys as their predicates. Zone maps and materialized views are popular approaches adopted by more extensive databases to address this issue. However, they are absent in in-memory databases because of space constraints. Instead, in-memory databases load the cache with result pages of frequently accessed queries. Increasing the number of such queries can fill the cache and raise the system’s overhead. This paper presents Query_Dictionary, a hybrid storage solution that leverages the full capabilities of SQLite by retaining less information of repetitive queries in the cache and efficiently accommodating the newly updated data by the end-user. The solution proposes storing page-level metadata query information for a larger result set and row-level information for a smaller result set. It demonstrates Query_Dictionary capabilities on three types of representative queries: single table, binary join, and transactional queries on non-indexed attributes. In comparison with SQLite, the proposed method performs better.

Cloud computing is rapidly expanding because it allows users to save the development and implementation time on their work. It also reduces the maintenance and operational costs of the used systems. Furthermore, it enables the elastic use of any resource rather than estimating workload, which may be inaccurate, as database systems can benefit from such a trend. In this paper, we propose an algorithm that allocates the materialized view over cloudbased replica sets to enhance the database system's performance in stock market using a Peer-to-Peer architecture. The results show that the proposed model (MVCRS) improves the query processing time and network transfer cost by distributing the materialized views over cloudbased replica sets. Also, it has a significant effect on decision-making and achieving economic returns. [ABSTRACT FROM AUTHOR]

Materialized View Selection is one of the most studied problems in the database field, covering SQL and NoSQL technologies as well as different deployment infrastructures (centralized, parallel, cloud). This problem has become more complex with the arrival of data warehouses, being coupled with the physical design phase that aims at optimizing query performance. Selecting the best set of materialized views to optimize query performance is a challenging task. Given their importance and the complexity of their selection, several research efforts both from academia and industry have been conducted. Results are promising - some solutions are being implemented by commercial and open-source DBMSs -, but they do not factor in the following properties of nowadays analytical queries: (i) largescale queries, (ii) their dynamicity, and (iii) their high interaction. Studies to date fail to consider that complete set of properties. Considering the three properties simultaneously is crucial regarding today's analytical requirements, which involve dynamic and interactive queries. In this paper, we first present a concise state of the art of the materialized view selection problem (VSP) by analyzing its ecosystem. Secondly, we propose a proactive re-selection approach that considers the three properties concurrently. It features two main phases: offline and online. In the offline phase, we manage a set of the first queries based on a given threshold d by selecting materialized views through a hypergraph structure. The second phase manages the addition of new queries by scheduling them, updates the structure of the hypergraph, and selects new views by eliminating the least beneficial ones. Finally, extensive experiments are conducted using the Star Schema Benchmark data set to evaluate the effectiveness and efficiency of our approach. [ABSTRACT FROM AUTHOR]

In cloud computing environment hardware resources required for the execution of query using distributed relational database system are scaled up or scaled down according to the query workload performance. Complex queries require large scale of resources in order to complete their execution efficiently. The large scale of resource requirements can be reduced by minimizing query execution time that maximizes resource utilization and decreases payment overhead of customers. Complex queries or batch queries contain some common subexpressions. If these common subexpressions evaluated once and their results are cached, they can be used for execution of further queries. In this research, we have come up with an algorithm for query optimization, which aims at storing intermediate results of the queries and use these by-products for execution of future queries. Extensive experiments have been carried out with the help of simulation model to test the algorithm efficiency.

Many organizations require real-time analysis of their business data streams to make instantaneous decisions. Data streams are often multidimensional and at the lowest level of abstraction, yet analysts are interested in their multilevel interactive analyses across several dimensions. Online analytical processing (OLAP) is a technique whose utility has been proven for such an analysis. Traditionally, OLAP over streams has been achieved by coupling a stream processing engine (SPE) with an OLAP engine. However, for many organizations, this is not an effective solution as it results in lower performance, resource wastage, and increased complexity and maintenance costs. Hence, we present StreamingCube, which is a unified framework for stream processing and OLAP analysis. The idea of a unified framework is supported by the observation that the incremental computation in stream processing and the maintenance of the materialized view of OLAP have many similarities. To seamlessly integrate the SPE and OLAP, as well as to maintain the OLAP lattice vertices incrementally, a cubify operator is introduced. StreamingCube supports two types of queries, namely, continuous queries (CQs) and OLAP queries. To demonstrate the effectiveness of the proposed framework, a detailed experimental evaluation is presented herein.

Modern database systems desperate for the ability to support highly scalable transactions and efficient queries simultaneously for real-time applications. One solution is to utilize query optimization techniques on the on-line transaction processing (OLTP) systems. The materialized view is considered as a panacea to decrease query latency. However, it also involves the significant cost of maintenance which trades away transaction performance. In this paper, we examine the design space and conclude several design features for the implementation of a view on a distributed log-structured merge-tree (LSM-tree), which is a well-known structure for improving data write performance. As a result, we develop two incremental view maintenance (IVM) approaches on LSM-tree. One avoids join computation in view maintenance transactions. Another with two optimizations is proposed to decouple the view maintenance with the transaction process. Under the asynchronous update, we also provide consistency queries for views. Experiments on TPC-H benchmark show our methods achieve better performance than straightforward methods on different workloads. [ABSTRACT FROM AUTHOR]

In cloud computing environment hardware resources required for the execution of query using distributed relational database system are scaled up or scaled down according to the query workload performance. Complex queries require large scale of resources in order to complete their execution efficiently. The large scale of resource requirements can be reduced by minimizing query execution time that maximizes resource utilization and decreases payment overhead of customers. Complex queries or batch queries contain some common subexpressions. If these common subexpressions evaluated once and their results are cached, they can be used for execution of further queries. In this research, we have come up with an algorithm for query optimization, which aims at storing intermediate results of the queries and use these by-products for execution of future queries. Extensive experiments have been carried out with the help of simulation model to test the algorithm efficiency. [ABSTRACT FROM AUTHOR]

This article targets the Incremental View Maintenance (IVM) of sophisticated analytics (such as statistical models, machine learning programs, and graph algorithms) expressed as linear algebra programs. We present LAGO, a unified framework for linear algebra that automatically synthesizes efficient incremental trigger programs, thereby freeing the user from error-prone manual derivations, performance tuning, and low-level implementation details. The key technique underlying our framework is abstract interpretation, which is used to infer various properties of analytical programs. These properties give the reasoning power required for the automatic synthesis of efficient incremental triggers. We evaluate the effectiveness of our framework on a wide range of applications from regression models to graph computations. [ABSTRACT FROM AUTHOR]

Materialized views are important for optimizing Business Intelligence (BI) systems when they are designed without data cubes. Selecting candidate queries from large number of queries for materialized views is a challenging task. Most of the work done in the past involves finding out frequent queries from the past workload and creating materialized views from such queries by either manually analyzing workload or using approximate string matching algorithms using query text. Most of the existing methods suggest complete queries but ignore query components such as sub queries for creation of materialized views. This paper presents a novel method to determine on which queries and query components materialized views can be created to optimize aggregate and join queries by mining database of query execution plans which are in the form of binary trees. The proposed algorithm showed significant improvement in terms of more number of optimized queries because it is using the execution plan tree of the query as a basis of selection of query to be optimized using materialized views rather than choosing query text which is used by traditional methods. For selecting a correct set of queries to be optimized using materialized views, the paper proposes efficient specialized frequent tree component mining algorithm with novel heuristics to prune search space. These frequent components are used to determine the possible set of candidate queries for creation of materialized views. Experimentation on standard, real and synthetic data sets, and also the theoretical basis, proved that the proposed method is able to optimize a large number of queries with less number of materialized views and showed a significant improvement in performance compared to traditional methods. [ABSTRACT FROM AUTHOR]

Views materialization is a powerful technique for optimization of query evaluation. However, when data sources are updated, these materialized views (MVs) should be maintained in order to get appropriate queries' answers. This work provides an overview of the research field of view maintenance, focusing on view maintenance issues and dimensions that cover the most important ones proposed in the literature review. We provide a classification and review the state-of-the-art of view maintenance approaches according to several parameters characterizing their algorithms, including information considered during the view maintenance process, principle used, and the execution mechanism. We provide the most important performance evaluation metrics of view maintenance process while performing a comparative study of some important approaches. Lastly, we give comprehensive perspectives on view maintenance in the different promising environments of MV applications. [ABSTRACT FROM AUTHOR]

Highlights • Approach to support performance comparisons in a federation of data marts. • Multidimensional queries over a global model integrating local sources. • An extension to the multidimensional model with mathematical formulas for indicators. • A query reformulation approach exploiting aggregation and indicator decomposition. • Computational analysis of the reformulation algorithm and proof of correctness. Abstract Measurement and comparison of performances in networked organisations is particularly critical because of heterogeneity and sparsity of data. In particular, each organization is autonomous in the definitions of which measures to use and their calculation formulas, i.e. the mathematical expressions stating how a measure is calculated from others. Hence, full integration of data marts requires a reconciliation among such heterogeneous definitions in order to support evaluation of cross-organizations performances and to produce meaningful comparisons. To address this issue, this paper proposes (1) an extension of the traditional multidimensional model by taking into account the explicit representation of the semantics for measure formulas, and, on the top of this model, (2) a novel query reformulation approach for a scenario of federated data warehouses. The approach exploits both aggregation and, unlike traditional approaches, measure decomposition through the calculation of measure formulas. This extends usual features of query rewriting based on views, allowing to overcome heterogeneities at measure level among data mart schemas and enabling meaningful comparisons among values of different autonomous data marts. A formalization of the rewriting algorithm is proposed, together with a computational analysis, proofs of correctness and termination, and an evaluation of effectiveness that shows how the approach can lead to a significant increase in the capability of integrating indicators to answer queries in a federated scenario. [ABSTRACT FROM AUTHOR]

This paper proposes a reinforcement learning based message transfer model for transferring news report messages through a selected path in a trusted provenance network with the objective of maximizing the reward values based on trust or importance based and network congestion or utility based cost measures. The reward values are calculated along a dynamically defined policy path connecting start topic or event node to a goal topic or event or issue nodes for incrementally defined time windows for a given network congestion situation. A hierarchy of agents of trusted roles is used to accomplish the sub-goals associated with sub-story or subtopic in the provenance structure where an agent role may assume the semantic role of the associated sub-topic. The twitted news story thread or plan of events is defined in this work from the starting topic or event node to the goal topic or event node for incrementally defined intervals of time. The graphs are clustered into subtopic and these sub-goals or sub topic nodes of a topic node at every level of granularity are associated with cluster of news reports which describe activities associated with sub-goal or sub-topic events. Such cluster of nodes may also represent drilled down sequence of sub-events describing a sub-topic or sub-goal node. The policy path in a topic or story graph model is defined by applying reinforcement learning principles on dynamically defined event models associated with evolution of topic definition observed from incrementally acquired samples of input training data spanning multiple time windows. We provide a methodology for unifying similar provenance graph models for adapting and averaging the policy path classifiers associated with individual models to produce a reduced set of unified models derived during training. A minimum set cover of classifiers is identified for the models and a clustering procedure of the models is suggested based on these classifiers. Other database clustering methods have also been suggested as alternatives for clustering these models. A collection of unified models are identified from the models identified within a cluster and the policy path classifiers associated with these models provide the story or topic descriptions destined to goal topic or event nodes characterizing these models within a cluster. [ABSTRACT FROM AUTHOR]

With the widespread use of the eXtensible Markup Language (XML), more and more applications store and query XML documents in XML database systems. Thus, how to efficiently process a query and find the specified patterns conforming the query from XML documents is a crucial issue. In this paper, some processing methods are employed on XML documents to improve document retrieval. First, a materialized view is built from an original document for each query. Then, on each materialized view, auxiliary structures such as T-Bitmap and indexes are also built to further accelerate query processing. Finally, four experiments are conducted to show the superiority of the proposed approach. [ABSTRACT FROM AUTHOR]

Materialized views are logically excess stored query results in SQL-oriented databases. This technology can significantly improve the performance of database systems. Although the idea of materialized views came up in the 1980s, only three database management systems, i.e. DB2, Oracle, SQL Server, have been successfully developed completely enough with materialized views so far. The barrier lies in building a module that can incrementally update the materialized views automatically, which corresponds to data changes in the base tables. This paper presents the algorithm to incrementally update the materialized views with inner join, focusing on one with aggregate functions, and building of a program that automatically generates codes inPL/pgSQL for triggers, which can undertake synchronous incremental updates of the materialized views in PostgreSQL. [ABSTRACT FROM AUTHOR]

Storage of pre-computed views in data warehouse can essentially reduce query processing cost for decision support queries. The problem is to choose an optimal set of materialized views. Various frameworks such as lattice, MVPP and AND-OR graphs and algorithms like heuristic based, greedy, stochastic algorithm have been proposed in the literature for materialized view selection. Heuristic and greedy algorithms become slower in high dimensional search space while stochastic algorithms do not guarantee global optimal solution but reach to the optimum most solution in a fast and efficient way. In this paper we have implemented Particle Swarm Optimization (PSO) algorithm, one of the stochastic algorithm, on lattice framework to select an optimal set of views for materialization in data warehouse by minimizing query processing cost. We have compared our results with Genetic algorithm to prove the effectiveness of PSO algorithm over genetic algorithm. [ABSTRACT FROM AUTHOR]

In recent years the inverted lists evaluation model along with holistic stack-based algorithms have been established as the most prominent techniques for evaluating XML queries on large persistent XML data. In this framework, we are using materialized views for optimizing XML queries. We consider a novel approach which instead of materializing the answer of a view materializes exactly the inverted sublists that are necessary for computing the answer of the view. This originality allows storing view materializations as compressed bitmaps, a solution that minimizes the materialization space and empowers performing optimization operations as CPU-efficient bitwise operations. To realize the potential of bitmap materialized views in optimizing query performance, we define and address the following problem (view configuration problem): given an XML tree and its schema find a template of tree-pattern views (view configuration) such that: (a) the views of this configuration can answer all the queries that can be issued against the schema, (b) their materialization fits in the space provided, and (c) evaluating the queries using these views minimizes the overall query evaluation cost. We consider an instance of this problem for tree pattern queries. Our intension is to find view configurations whose materializations are small enough to be stored in main memory. We find two candidate solution configurations and we identify cases where views can be excluded from materialization in a configuration without affecting query performance. In order to compare our approach with an approach which also can support the optimization of every query on the schema, we implemented an improvement of a state-of-the-art approach which is based on structural indexes. Our experimental results show that our approach is stable, greatly improves evaluating queries without materialized views, outperforms the structural index approach on all test cases and is very close to the optimal. These results characterize our approach as the best candidate for supporting the optimization of queries in the framework of the inverted lists model. [ABSTRACT FROM AUTHOR]

Applications ranging from algorithmic trading to scientific data analysis require real-time analytics based on views over databases receiving thousands of updates each second. Such views have to be kept fresh at millisecond latencies. At the same time, these views have to support classical SQL, rather than window semantics, to enable applications that combine current with aged or historical data. In this article, we present the DBToaster system, which keeps materialized views of standard SQL queries continuously fresh as data changes very rapidly. This is achieved by a combination of aggressive compilation techniques and DBToaster's original recursive finite differencing technique which materializes a query and a set of its higher-order deltas as views. These views support each other's incremental maintenance, leading to a reduced overall view maintenance cost. DBToaster supports tens of thousands of complete view refreshes per second for a wide range of queries. [ABSTRACT FROM AUTHOR]

Abstract: The view- and index-selection problem is a combinatorial optimization problem that arises in the context of on-line analytical processing (OLAP) in database-management systems. We propose an integer programming (IP) model for this problem and study the properties of the views and indexes that appear in the optimal solution for this model. We then use these properties to remove a number of variables and constraints from the corresponding IP model and obtain a model that is significantly smaller, yet its optimal solution is guaranteed to be optimal for the original problem. This allows us to solve realistic-size instances of the problem in reasonable time using commercial IP solvers. Subsequently, we propose heuristic strategies to further reduce the size of this IP model and dramatically reduce its execution time, although we no longer guarantee that the reduced IP model offers a globally optimal solution for the original problem. Finally, we carry out an extensive computational study to evaluate the effectiveness of these IP models for solving the OLAP view- and index-selection problem. [Copyright &y& Elsevier]