Optimization of nested SQL queries revisited

Ganski, Richard A.; Wong, Harry K. T.

doi:10.1145/38714.38723

Cited by 39 publications

(22 citation statements)

References 6 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…A key challenge in query optimization is how to ensure that the rewritten query is indeed semantically equivalent to the input, i.e., that the original and rewritten queries return the same results when executed on all possible input database instances. History suggests that the lack of tools to establish such equivalences has caused longstanding, latent bugs in major database systems [32], and new bugs continue to arise [7,10]. All such errors lead to incorrect results returned that can cause dire consequences.…”

Section: Introductionmentioning

confidence: 99%

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

et al. 2018

View full text Add to dashboard Cite

Deciding the equivalence of SQL queries is a fundamental problem in data management. As prior work has mainly focused on studying the theoretical limitations of the problem, very few implementations for checking such equivalences exist. In this paper, we present a new formalism and implementation for reasoning about the equivalences of SQL queries. Our formalism, Usemiring, extends SQL's semiring semantics with unbounded summation and duplicate elimination. U-semiring is defined using only very few axioms and can thus be easily implemented using proof assistants such as Coq for automated query reasoning. Yet, they are sufficient enough to enable us reason about sophisticated SQL queries that are evaluated over bags and sets, along with various integrity constraints. To evaluate the effectiveness of U-semiring, we have used it to formally verify 68 equivalent queries and rewrite rules from both classical data management research papers and real-world SQL engines, where many of them have never been proven correct before.

show abstract

Section: Introductionmentioning

confidence: 99%

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

et al. 2018

View full text Add to dashboard Cite

show abstract

“…This makes it a challenging topic for query optimization. This is reflected by the attention the area has received in previous work, e.g., [3,4,5,6,7,8]. While [9] has specifically focused on optimization of subqueries with grouping at the logical level, this current paper takes a broader perspective on the problem.…”

Section: Challenges and Contributionsmentioning

confidence: 99%

Execution strategies for SQL subqueries

Elhemali

Galindo-Legaria

Grabs

et al. 2007

Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data

View full text Add to dashboard Cite

Optimizing SQL subqueries has been an active area in database research and the database industry throughout the last decades. Previous work has already identified some approaches to efficiently execute relational subqueries. For satisfactory performance, proper choice of subquery execution strategies becomes even more essential today with the increase in decision support systems and automatically generated SQL, e.g., with ad-hoc reporting tools. This goes hand in hand with increasing query complexity and growing data volumes -which all pose challenges for an industrial-strength query optimizer.This current paper explores the basic building blocks that Microsoft SQL Server utilizes to optimize and execute relational subqueries. We start with indispensable prerequisites such as detection and removal of correlations for subqueries. We identify a full spectrum of fundamental subquery execution strategies such as forward and reverse lookup as well as set-based approaches, explain the different execution strategies for subqueries implemented in SQL Server, and relate them to the current state of the art. To the best of our knowledge, several strategies discussed in this paper have not been published before.An experimental evaluation complements the paper. It quantifies the performance characteristics of the different approaches and shows that indeed alternative execution strategies are needed in different circumstances, which make a cost-based query optimizer indispensable for adequate query performance.

show abstract

“…Deshpande et al [7] have explained the cacheand-query for wide area sensor databases. Ganski and Wong [8] have defined the optimization of nested SQL queries revisited. Gomaa [9] has described the concurrent object modelling and architectural design method for the development of concurrent applications, in particular for distributed and real time applications with the use of UML.…”

Section: Related Workmentioning

confidence: 99%

Object Oriented Query Response Time for UML Models

Saxena¹,

Kumar²

2012

JSEA

View full text Add to dashboard Cite

Nowadays, the size of database of any business organization is increasing and many of the companies are shifted the old structured database into the object oriented database. Due to increase of size of database complexity of database is increasing therefore, it is necessary to optimize the object oriented query response time from the complex object oriented database. In the present paper, a real case study of Life Insurance Corporation of India is taken and sample object oriented database is designed by the use of SQL Server 2008. A UML model is designed for computing the object oriented query response time. Table and graph are also represented for the computed records in five runs.

show abstract

Optimization of nested SQL queries revisited

Cited by 39 publications

References 6 publications

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

Execution strategies for SQL subqueries

Object Oriented Query Response Time for UML Models

Contact Info

Product

Resources

About