Query evaluation techniques for large databases
ACM Computing Surveys (CSUR)
Outerjoin simplification and reordering for query optimization
ACM Transactions on Database Systems (TODS)
On optimizing an SQL-like nested query
ACM Transactions on Database Systems (TODS)
ICDE '96 Proceedings of the Twelfth International Conference on Data Engineering
PDIS '93 Proceedings of the 2nd International Conference on Parallel and Distributed Information Systems
Translating and Optimizing SQL Queries Having Aggregates
VLDB '87 Proceedings of the 13th International Conference on Very Large Data Bases
Improved Unnesting Algorithms for Join Aggregate SQL Queries
VLDB '92 Proceedings of the 18th International Conference on Very Large Data Bases
Unit-testing query transformation rules
Proceedings of the 1st international workshop on Testing database systems
Exploiting Interactions among Query Rewrite Rules in the Teradata DBMS
DEXA '08 Proceedings of the 19th international conference on Database and Expert Systems Applications
Provenance for nested subqueries
Proceedings of the 12th International Conference on Extending Database Technology: Advances in Database Technology
Enhanced subquery optimizations in Oracle
Proceedings of the VLDB Endowment
A statistical approach towards robust progress estimation
Proceedings of the VLDB Endowment
Program analysis and transformation for holistic optimization of database applications
Proceedings of the ACM SIGPLAN International Workshop on State of the Art in Java Program analysis
Robust estimation of resource consumption for SQL queries using statistical techniques
Proceedings of the VLDB Endowment
Keyword search on form results
The VLDB Journal — The International Journal on Very Large Data Bases
On the correct and complete enumeration of the core search space
Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data
| Hi-index | 0.00 |
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.