10 November 2017 in IEEE Transactions on Knowledge and Data Engineering
Today, many scientific data sets are open to the public. For their operators, it is important to know what the users are interested in. In this paper, we study the problem of extracting and analyzing patterns from the query log of a database. We focus on design errors (antipatterns), which typically lead to unnecessary SQL statements. Such antipatterns do not only have a negative effect on performance. They also introduce bias on any subsequent analysis of the SQL log. We propose a framework designed to discover patterns and antipatterns in arbitrary SQL query logs and to clean antipatterns. To study the usefulness of our approach and to reveal insights regarding the existence of antipatterns in real-world systems, we examine the SQL log of the SkyServer project, containing more than 40 million queries. Among the top 15 patterns, we have found 6 antipatterns. This result as well as other ones gives way to the conclusion that antipatterns might falsify refactoring and any other downstream analyses.
11 October 2019 in Information Systems
26 April 2019 in IEEE Transactions on Knowledge and Data Engineering
In the sciences and elsewhere, the use of relational databases has become ubiquitous. An important challenge is finding hot spots of user interests. In principle, one can discover user interests by clustering the queries in the query log. Such a clustering requires a notion of query similarity. This, in turn, raises the question of what features of SQL queries are meaningful. We have studied the query representations proposed in the literature and corresponding similarity functions and have identified shortcomings of all of them. To overcome these limitations, we propose new similarity functions for SQL queries. They rely on the so-called access area of a query and, more specifically, on the overlap and the closeness of the access areas. We have carried out experiments systematically to compare the various similarity functions described in this article. The first series of experiments measures the quality of clustering and compares it to a ground truth. In the second series, we focus on the query log from the well-known SkyServer database. Here, a domain expert has interpreted various clusters by hand. We conclude that clusters obtained with our new measures of similarity seem to be good indicators of user interests.
10 April 2018 in IEEE Transactions on Knowledge and Data Engineering
Efficient evaluation of selection predicates is a performance-critical task. With analytical queries getting more complex, the number of selection predicates per query and table rises. Recent approaches to increase performance of main-memory databases aim at exploiting the CPU speed by using accelerated scans. However, scanning each column leaves tuning opportunities open that arise considering all predicates together. We introduce Elf, an index structure that is able to exploit the relation between several selection predicates. Elf features cache sensitivity, an optimized storage layout, fixed search paths, and data compression. We compare its query performance to state-of-the-art approaches and a scan using SIMD. Our results indicate a clear superiority of our approach for queries returning less than 10% of all tuples — a selectivity almost one magnitude larger than observed for related indexing approaches. For TPC-H queries with multi-column selection predicates, we achieve a speedup between factor five and two orders of magnitude, mainly depending on the selectivity of the predicates. Further experiments reveal that for large data sets, these speedup factors are expected to increase. Finally, our results indicate that using a delta-store like concept results in virtually no performance penalty for reasonable sizes of a write-optimized Elf as delta store.
1 November 2021 in Information Systems