Surajit Chaudhuri
ABSTRACT
Parameterized queries are commonly used in database applications. In a parameterized query, the same SQL statement is potentially executed multiple times with different parameter values. In today's DBMSs the query optimizer typically chooses a single execution plan that is reused for multiple instances of the same query. A key problem is that even if a plan with low average cost across instances is chosen, its variance can be high, which is undesirable in many production settings. In this paper, we describe techniques for selecting a plan that can better address the trade-off between the average and variance of cost across instances of a parameterized query. We show how to efficiently compute the skyline in the average-variance cost space. We have implemented our techniques on top of a commercial DBMS. We present experimental results on benchmark and real-world decision support queries.
- Babcock, B. et al. Towards a Robust Query Optimizer: A Principled and Practical Approach. SIGMOD 2005. Google Scholar
Digital Library
- Bizarro, P., Bruno, N., DeWitt, D. J. Progressive Parametric Query Optimization. IEEE TKDE g. 21, 4 (2009), 582--594. Google ScholarDigital Library
- Borzsonyi, S., Kossmann, D., Stocker, K. The skyline operator. ICDE 2001. Google ScholarDigital Library
- Chaudhuri, S. Query optimizers: time to rethink the contract? SIGMOD 2008. Google ScholarDigital Library
- Chaudhuri, S. et al. Primitives for Workload Summarization and Implications for SQL. VLDB 2003. Google ScholarDigital Library
- Chaudhuri, S., Narasayya, V. Program for TPC-D Data Generation with skew. ftp://ftp.research.microsoft.com/users/viveknar/TPCDSkew/Google Scholar
- Chu, F., Halpern, J. Y., Gehrke, J. Least Expected Cost Query Optimization: What Can We Expect? PODS 2002. Google ScholarDigital Library
- Chu, F., Halpern, J. Y., Seshadri, P. Least Expected Cost Query Optimization: An Exercise in Utility. PODS 1999. Google ScholarDigital Library
- DB2. Optimizer profiles and guideline overview. http://publib.boulder.ibm.com/Google Scholar
- Deshpande, A. et al. Adaptive Query Processing. Foundations and Trends in Databases. 1, 1 (2007), 1--140. Google ScholarDigital Library
- Dey, A. et al. Efficiently Approximating Query Optimizer Plan Diagrams. PVLDB 2008. Google ScholarDigital Library
- Ghosh, A. et al. Plan Selection based on Query clustering. VLDB 2002. Google ScholarDigital Library
- Hutchison, D. et al. Multiobjective Optimization: Interactive and Evolutionary Approaches. Springer, 2008.Google Scholar
- Ioannidis, E. et al. Optimal histograms for limiting worst-case error propagation in the size of join results. TODS 1993. Google ScholarDigital Library
- Ioannidis, Y. E., Ng, R. T., Shim, K., Sellis, T. K. Parametric query optimization. The VLDB Journal (1997) 6, 132--151. Google ScholarDigital Library
- Oracle. Using Query Hints. http://www.oracle.comGoogle Scholar
- Qiao, L. et al. Main-Memory Scan Sharing For Multi-Core CPUs. PVLDB 2008. Google ScholarDigital Library
- Raman, V. et al. Constant-Time Query Processing. ICDE'08. Google ScholarDigital Library
- Reddy, N., Haritsa, J. R. Analyzing Plan Diagrams of Database Query Optimizers. VLDB 2005. Google ScholarDigital Library
- SQL Server Books Online, Hints (Transact-SQL). 2009. http://technet.microsoft.com/en-us/library/ms187713.aspxGoogle Scholar
- TPC-H Benchmark. http://www.tpc.org/tpch/Google Scholar
- Unterbrunner, U. et al. Predictable Performance for Unpredictable Workloads. In PVLDB 2009. Google ScholarDigital Library
Index Terms
- Variance aware optimization of parameterized queries
Recommendations
Optimization of joins using random record generation method
A2CWiC '10: Proceedings of the 1st Amrita ACM-W Celebration on Women in Computing in IndiaJoins are statements that retrieve data from more than one table. A Join is characterized by multiple tables in the FROM clause, and the relationship between the tables is defined through the existence of a Join condition in the WHERE clause. In the ...
Leveraging Re-costing for Online Optimization of Parameterized Queries with Guarantees
SIGMOD '17: Proceedings of the 2017 ACM International Conference on Management of DataParametric query optimization (PQO) deals with the problem of finding and reusing a relatively small number of plans that can achieve good plan quality across multiple instances of a parameterized query. An ideal solution to PQO would process query ...
Automating Statistics Management for Query Optimizers
Statistics play a key role in influencing the quality of plans chosen by a database query optimizer. In this paper, we identify the statistics that are essential for an optimizer. We introduce novel techniques that help significantly reduce the set of ...
Comments