Execution strategies for SQL subqueries

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 dif...

• Type: Conference
• Year: 2007
• Where: SIGMOD
• Authors: César A. Galindo-Legaria, Milind Joshi, Mostafa Elhemali, Torsten Grabs
• Keywords: Database, Industrial-strength Query Optimizer, Relational Subqueries, SIGMOD 2007, SQL Subqueries