BLOCKIN BLOCKINÒ BLOCKIN× ½¸ÔÔº ¿ßß¿º ¿

ACM Transactions on Database Systems

Improved techniques are disclosed for processing data stream queries wherein a data stream is obtained, a set of aggregate queries to be executed on the data stream is obtained, and a query plan for executing the set of aggregate queries on the data stream is generated. In a first method, the generated query plan includes generating at least one intermediate aggregate query, wherein the intermediate aggregate query combines a subset of aggregate queries from the set of aggregate queries so as to pre-aggregate data from the data stream prior to execution of the subset of aggregate queries such that the generated query plan is optimized for computational expense based on a given cost model. In a second method, the generated query plan includes identifying similar filters in two or more aggregate queries of the set of aggregate queries and combining the similar filters into a single filter such that the single filter is usable to pre-filter data input to the two or more aggregate queries.

Method and apparatus for efficient aggregate computation over data streams

• A submitted manuscript is the version of the article upon submission and before peer-review. There can be important differences between the submitted version and the official published version of record. People interested in the research are advised to contact the author for the final version of the publication, or visit the DOI to the publisher's website. • The final author version and the galley proof are versions of the publication after peer review. • The final published version features the final layout of the paper including the volume, issue and page numbers.

/pdf/designing-algorithms-for-big-graph-datasets-a-study-of-181ube0cjd.pdf

Designing algorithms for big graph datasets : a study of computing bisimulation and joins

This paper describes a very high-level language for clear description of distributed algorithms and optimizations necessary for generating efficient implementations. The language supports high-level control flows where complex synchronization conditions can be expressed using high-level queries, especially logic quantifications, over message history sequences. Unfortunately, the programs would be extremely inefficient, including consuming unbounded memory, if executed straightforwardly.We present new optimizations that automatically transform complex synchronization conditions into incremental updates of necessary auxiliary values as messages are sent and received. The core of the optimizations is the first general method for efficient implementation of logic quantifications. We have developed an operational semantics of the language, implemented a prototype of the compiler and the optimizations, and successfully used the language and implementation on a variety of important distributed algorithms.

From clarity to efficiency for distributed algorithms

Despite extensive research on visual query systems, the standard way to interact with relational databases remains to be through SQL queries and tailored form interfaces. We consider three requirements to be essential to a successful alternative: (1) query specification through direct manipulation of results, (2) the ability to view and modify any part of the current query without departing from the direct manipulation interface, and (3) SQL-like expressiveness. This paper presents the first visual query system to meet all three requirements in a single design. By directly manipulating nested relational results, and using spreadsheet idioms such as formulas and filters, the user can express a relationally complete set of query operators plus calculation, aggregation, outer joins, sorting, and nesting, while always remaining able to track and modify the state of the complete query. Our prototype gives the user an experience of responsive, incremental query building while pushing all actual query processing to the database layer. We evaluate our system with formative and controlled user studies on 28 spreadsheet users; the controlled study shows our system significantly outperforming Microsoft Access on the System Usability Scale.

/pdf/expressive-query-construction-through-direct-manipulation-of-15yjl28hsi.pdf

Expressive Query Construction through Direct Manipulation of Nested Relational Results

Most research work on optimization of nested queries focuses on aggregate subqueries. In this article, we show that existing approaches are not adequate for nonaggregate subqueries, especially for those having multiple subqueries and certain comparison operators. We then propose a new efficient approach, the nested relational approach, based on the nested relational algebra. The nested relational approach treats all subqueries in a uniform manner, being able to deal with nested queries of any type and any level. We report on experimental work that confirms that existing approaches have difficulties dealing with nonaggregate subqueries, and that the nested relational approach offers better performance. We also discuss algebraic optimization rules for further optimizing the nested relational approach and the issue of integrating it into relational database systems.

SQL query optimization through nested relational algebra

One of the most powerful features of SQL is the use of nested queries. Most research work on the optimization of nested queries focuses on aggregate subqueries. However, the solutions proposed for non-aggregate subqueries are still limited, especially for queries having multiple subqueries and null values. In this paper, we show that existing approaches to queries containing non-aggregate subqueries proposed in the literature (including rewrites) are not adequate. We then propose a new efficient approach, the nested relational approach, based on the nested relational algebra. Our approach directly unnests non-aggregate subqueries using hash joins, and treats all subqueries in a uniform manner, being able to deal with nested queries of any type and any level. We report on experimental work that confirms that existing approaches have difficulties dealing with non-aggregate subqueries, and that our approach offers better performance. We also discuss some possibilities for algebraic optimization and the issue of integrating our approach in a relational database system.

/pdf/a-nested-relational-approach-to-processing-sql-subqueries-3n2bw8ww8o.pdf

A nested relational approach to processing SQL subqueries

MySQL is an OLTP system without the ability to handle SQL subqueries. As part of our project in query optimization, we add subquery processing to MySQL through rewriting. First, we incorporate the ability to handle subqueries in the FROM clause; then rewrite queries with subqueries in the WHERE clause by moving the subquery to the FROM clause and introducing suitable conditions to link the subquery and the main query tables. The result is a complete and correct unnesting procedure for SQL queries. The idiosyncrasies of SQL semantics make some rewritings tricky. While optimization issues are not directly addressed, the rewriting opens up the possibility of more efficient query processing in several cases.

/pdf/adding-subqueries-to-mysql-what-does-it-take-to-have-a-3z1nwyy6iv.pdf

Adding subqueries to MySQL, what does it take to have a decision-support engine?

Detecting and dealing with redundancy is an ubiquitous problem in query optimization, which manifests itself in many areas of research such as materialized views, multi-query optimization, and query-containment algorithms. In this paper, we focus on the issue of intra-query redundancy, redundancy present within a query. We present a method to detect the maximal redundancy present between a main (outer) query block and a subquery block. We then use the method for query optimization, introducing query plans and a new operator that take full advantage of the redundancy discovered. Our approach can deal with redundancy in a wider spectrum of queries than existing techniques. We show experimental evidence that our approach works under certain conditions, and compares favorably to existing optimization techniques when applicable.

Exploiting maximal redundancy to optimize SQL queries

It is well known that SQL's syntax sometimes forces users to write queries in an awkward way. Together with the danger of formulating an incorrect query, complex queries pose a challenge to the optimizer. A well studied example is that of universal quantification[1,2]. As an example, assume two relations: student(sid) and teaches(pid,sid) , which denotes that professor pid is a teacher of student sid . Consider the question "find the professors teaching allstudents." Since SQL does not directly support the quantifier all, most textbooks express this question using two subqueries, NOT EXISTS and NOT IN .

Bin Cao

Papers

SQL query optimization through nested relational algebra

A nested relational approach to processing SQL subqueries

Adding subqueries to MySQL, what does it take to have a decision-support engine?

Exploiting maximal redundancy to optimize SQL queries

An Implementation of a Query Language with Generalized Quantifiers