Eric N. Hanson

A performance analysis of view materialization strategies

The conventional way to process commands for relational views is to use query modification to translate the commands into ones on the base relations. An alternative approach has been proposed recently, whereby materialized copies of views are kept, and incrementally updated immediately after each modification of the database. A related scheme exists, in which update of materialized views is deferred until just before data is retrieved from the view. A performance analysis is presented comparing the cost of query modification, immediate view maintenance, and deferred view maintenance. Three different models of the structure of views are given a simple selection and projection of one relation, the natural join of two relations, and an aggregate (e.g. the sum of values in a column) over a selection-projection view. The results show that the choice of the most efficient view maintenance method depends heavily on the structure of the database, the view definition, and the type of query and update activity present.

Extending a database system with procedures

This paper suggests that more powerful database systems (DBMS) can be built by supporting database procedures as full-fledged database objects. In particular, allowing fields of a database to be a collection of queries in the query language of the system is shown to allow the natural expression of complex data relationships. Moreover, many of the features present in object-oriented systems and semantic data models can be supported by this facility.nIn order to implement this construct, extensions to a typical relational query language must be made, and considerable work on the execution engine of the underlying DBMS must be accomplished. This paper reports on the extensions for one particular query language and data manager and then gives performance figures for a prototype implementation. Even though the performance of the prototype is competitive with that of a conventional system, suggestions for improvement are presented.

The POSTGRES rule manager

The rule subsystem that is being implemented in the POSTGRES DBMS is explained. It is novel in several ways. First, it gives users the capability of defining rules as well as data. Moreover, depending on the scope of each rule defined, optimization is handled differently. This leads to good performance both when there are many rules each of small scope and when there are a few rules each of large scope. In addition, rules provide either a forward-chaining or a backward-chaining control flow, and the system chooses the control mechanism that optimizes performance whenever possible. Priority rules can be defined, allowing a user to specify rule systems that have conflicts. This use of exceptions seems necessary in many applications. Database services such as views, protection, integrity constraints, and referential integrity can be obtained simply by applying the rules system in the appropriate way. Consequently, no special-purpose code need be included in POSTGRES to handle these tasks. >

The design of the POSTGRES rules system

This paper explains the rules subsystem that is being implemented in the POSTGRES DBMS. It is novel in several ways. First, it gives to users the capability of defining rules as well as data to a DBMS. Moreover, depending on the scope of each rule defined, optimization is handled differently. This leads to good performance both in the case that there are many rules each of small scope and a few rules each of large scope. In addition, rules provide either a forward chaining control flow or a backward chaining one, and the system will choose the control mechanism that optimizes performance in the cases that it is possible. Furthermore, priority rules can be defined, thereby allowing a user to specify rules systems that have conflicts. This use of exceptions seems necessary in many applications. Lastly, our rule system can support an implementation of views, protection and integrity control, simply by applying the rules system in a particular way. Consequently, no special purpose code need be included to handle these tasks.

A performance comparison of the Rete and TREAT algorithms for testing database rule conditions

The authors present the results of a simulation comparing the performance of the two most widely used production rule condition testing algorithms, Rete and TREAT, in the context of a database rule system. The results show that TREAT almost always outperforms Rete. TREAT requires less storage than Rete, and is less sensitive to optimization decisions than Rete. Based on these results, it is concluded that TREAT is the preferred algorithm for testing join conditions of database rules. Since Rete does outperform TREAT in some cases, this study suggests a next step which would be to develop a hybrid version of Rete and TREAT with an optimizer that would decide which strategy to use based on the rule definition and statistics about the data and update patterns.<<ETX>>

Processing queries aganist database procedures: a performance analysis

A database procedure is a collection of queries stored in the database. Several methods are possible for processing queries that retrieve the value returned by a database procedure. The conventional algorithm is to execute the queries in a procedure whenever it is accessed. A second strategy requires caching the previous value returned by the database procedure. If the cached value is valid at the time of a query, the value is returned immediately. If the cached value has been invalidated by an update, the value is recomputed, stored back into the cache, and then returned. A third strategy uses a differential view maintenance algorithm to maintain an up-to-date copy of the value returned by the procedure. This paper compares the performance of these three alternatives. The results show that which algorithm is preferred depends heavily on the database environment, particularly, the frequency of updates and the size of objects retrieved by database procedures.

BIPie: Fast Selection and Aggregation on Encoded Data using Operator Specialization

Advances in modern hardware, such as increases in the size of main memory available on computers, have made it possible to analyze data at a much higher rate than before. In this paper, we demonstrate that there is tremendous room for improvement in the processing of analytical queries on modern commodity hardware. We introduce BIPie, an engine for query processing implementing highly efficient decoding, selection, and aggregation for analytical queries executing on a columnar storage engine in MemSQL. We demonstrate that these operations are interdependent, and must be fused and considered together to achieve very high performance. We propose and compare multiple strategies for decoding, selection and aggregation (with GROUP BY), all of which are designed to take advantage of modern CPU architectures, including SIMD. We implemented these approaches in MemSQL, a high performance hybrid transaction and analytical processing database designed for commodity hardware. We thoroughly evaluate the performance of the approach across a range of parameters, and demonstrate a two to four times speedup over previously published TPC-H Query 1 performance.