MultiQuery Optimization多查询优化.pptVIP

Multi-Query Optimization Prasan Roy Indian Institute of Technology - Bombay Overview Multi-Query Optimization: What? Problem statement Multi-Query Optimization: Why? Application scenarios Multi-Query Optimization: How? A cost-based practical approach Prototyping Multi-Query Optimization On MS SQL-Server at Microsoft Research prototype at IIT-Bombay Multi-Query Optimization: What? Exploit common subexpressions (CSEs) in query optimization Consider DAG execution plans in addition to tree execution plans Example Example (contd) Alternative: Multi-Query Optimization: Why? Queries on views, nested queries, … Overlapping query batches generated by applications Update expressions for materialized views Query invocations with different parameters . . . Multi-Query Optimization: How? Set up the search space Identify the common subexpressions Explore the search space efficiently Find the best way to exploit the common subexpressions Problems Materializing and sharing a CSE not necessarily cheaper Mutually exclusive alternatives (A JOIN B JOIN C) (B JOIN C JOIN D) (C JOIN D JOIN E) What to share: (B JOIN C) or (C JOIN D) ? Earlier Work: Practical Solutions As early as 1976 Preprocess query before optimization [Hall, IBM-JRD76] As late as 1998 Postprocess optimized plans [Subramanium and Venkataraman, SIGMOD98] Earlier Work: Theoretical Studies [Sellis, TODS88], [Cosar et al., CIKM93], [Shim et al., DKE94],... Set of queries {Q1, Q2, …, Qn} For each query Qi, set of execution plans {Pi1, Pi2, …, Pim} Pij is a set of tasks from a common pool Pick a plan for each query such that the cost of tasks in the union is minimized Microsoft Experience with Paul Larson, Microsoft Research Prototyping MQO on SQL-Server Add multi-query optimization capability to SQL-Server Well integrated with the existing optimization framework another optimization level minimal changes, minimal extra lines of code First cut: exhaustive How slow can i