Progressive Query Optimization for Federated Queries

  • Stephan Ewen
  • Holger Kache
  • Volker Markl
  • Vijayshankar Raman
Part of the Lecture Notes in Computer Science book series (LNCS, volume 3896)

Abstract

Database Management Systems (DBMS) perform query plan selection by mathematically modeling the execution cost of candidate execution plans and choosing the cheapest query execution plan (QEP) according to that cost model. The cost model requires accurate estimates of the sizes of intermediate results of all steps in the QEP. Outdated or incomplete statistics, parameter markers and complex skewed data frequently cause the selection of a suboptimal query plan, which in turn results in bad query performance. Federated queries are regular relational queries accessing data on one or more remote relational or non-relational data sources, possibly combining them with tables stored in the federated DBMS server. Their execution is typically divided between the federated server and the remote data sources. Outdated and incomplete statistics have a bigger impact on federated DBMS than on regular DBMS, as maintenance of federated statistics is unequally more complicated and expensive than the maintenance of the local statistics; consequently bad performance commonly occurs for federated queries due to the selection of a suboptimal query plan. We present an extension of the mid-query reoptimization technique "Progressive Query Optimization" (POP), which adds robustness to query processing by dynamically detecting if an access plan is suboptimal and by triggering a reoptimization in that case. Our extensions enable efficient reoptimization of federated queries. Our contributions are (a) an opportunistic, but risk controlled, reoptimization technique for federated DBMS (b) a technique for multiple reoptimizations during federated query processing, with a strategy to discover redundant and eliminate partial results and (c) a mechanism to eagerly procure statistics in a federated environment. We have implemented these techniques in a prototype version of WebSphere Information Integrator for DB2. Our enhancements enable robust and acceptable performance for federated queries, even if the remote data sources provided almost no statistical information about the data. An extensive case study on real world data shows POP has negligible runtime overhead and improves the performance of complex federated queries by up to a full order of magnitude.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Ahad, R., Rao, K.V.B., McLeod, D.: On Estimating the Cardinality of the Projection of a Database Relation. In: Proc. TODS (1989)Google Scholar
  2. 2.
    Antoshenkov, G., Ziauddin, M.: Query Processing and Optimization in Oracle Rdb. VLDB Journal 5 (1996)Google Scholar
  3. 3.
    Avnur, R., Hellerstein, J.M.: Eddies: Continuously Adaptive Query Processing. In: Proc. ACM SIGMOD (2000)Google Scholar
  4. 4.
    Babu, S., Bizarro, P., DeWitt, D.: Proactive Re-Optimization. In: Proc. ACM SIGMOD (2005)Google Scholar
  5. 5.
    Christodoulakis, S.: Implications of Certain Assumptions in Database Performance Evaluation. In: Proc. ACM Trans. on Database Systems (1984)Google Scholar
  6. 6.
    Du, W., Krishnamurthy, R., Shan, M.-C.: Query optimization in heterogeneous DBMS. In: VLDB (1992)Google Scholar
  7. 7.
    Gardarin, G., Sha, F., Tang, Z.-H.: Calibrating the query optimizer cost model of IRO-DB, an object-oriented federated database system. In: VLDB (1996)Google Scholar
  8. 8.
    Gassner, P., Lohman, G.M., Schiefer, K.B., Wang, Y.: Query Optimization in the IBM DB2 Family. IEEE Data Engineering Bulletin (1994)Google Scholar
  9. 9.
    Van Gelder, A.: Multiple Join Size Estimation by Virtual Domains. In: Proc. PODS (1993)Google Scholar
  10. 10.
    Graefe, G., Ward, K.: Dynamic query evaluation plans. In: Proc. ACM SIGMOD (1989)Google Scholar
  11. 11.
    Haas, L.M., Kossmann, D., Wimmers, E.L., Yang, J.: Optimizing Queries across Diverse Data Sources. In: Proc. VLDB 1997 (1997)Google Scholar
  12. 12.
    Ives, Z., Halevy, A., Weld, D.: Adapting to Source Properties in Processing Data Integration Queries. In: Proc. ACM SIGMOD (2004)Google Scholar
  13. 13.
    Kabra, N., DeWitt, D.: Efficient Mid-Query Re-Optimization of Suboptimal Query Execution Plans. In: Proc. ACM SIGMOD (1998)Google Scholar
  14. 14.
    Markl, V., Megiddo, N., Kutsch, M., Tran, T.M., Haas, P., Srivastava, U.: Consistently Estimating the Selectivity of Conjuncts of Predicates. In: Proc. VLDB (2005)Google Scholar
  15. 15.
    Markl, V., Raman, V., Simmen, D., Lohman, G., Pirahesh, H., Cilimdzic, M.: Robust Query Processing through Progressive Optimization. In: Proc. ACM SIGMOD (2004)Google Scholar
  16. 16.
    Raman, V., Hellerstein, J.: Partial Results for Online Query Processing. In: Proc. ACM SIGMOD (2002)Google Scholar
  17. 17.
    Raman, V., Deshpande, A., Hellerstein, J.: Using State Modules for Adaptive Query Processing. In: ICDE (2003)Google Scholar
  18. 18.
    Selinger, P.G., Astrahan, M.M., Chamberlain, D.D., Lorie, R.A., Price, T.G.: Access Path Selection in a Relational Database. In: Proc. ACM SIGMOD (1979)Google Scholar
  19. 19.
    Stillger, M., Lohman, G., Markl, V., Kandil, M.: LEO: DB2’s Learning Optimizer. In: Proc. VLDB (2001)Google Scholar
  20. 20.
    Stonebraker, M., Aoki, P.M., Devine, R., Litwin, W., Olson, M.: Mariposa: A New Architecture for Distributed Data, ICDE 1994. Also Sequoia 2000 TR 93/31, UC Berkeley (1993)Google Scholar
  21. 21.
    Swami, A.N., Schiefer, K.B.: On the Estimation of Join Result Sizes. In: Proc. EDBT (1994)Google Scholar
  22. 22.
    Urhan, T., Franklin, M.J., Amsaleg, L.: Cost Based Query Scrambling for Initial Delays. In: Proc. ACM SIGMOD (1998)Google Scholar
  23. 23.
    Zaharioudakis, M., Cochrane, R., Lapis, G., Pirahesh, H., Urata, M.: Answering Complex SQL Queries Using Automatic Summary Tables. In: Proc. ACM SIGMOD (2000)Google Scholar
  24. 24.
    Q. Zhu, P. Larson - Solving local cost estimation for global query optimization in multidatabase systems, Distributed and Parallel Databases, 6:1–51, 1998 Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2006

Authors and Affiliations

  • Stephan Ewen
    • 1
  • Holger Kache
    • 2
  • Volker Markl
    • 3
  • Vijayshankar Raman
    • 3
  1. 1.IBM GermanyHerrenbergGermany
  2. 2.IBM Silicon Valley LaboratorySan JoséUSA
  3. 3.IBM Almaden Research CenterSan JoséUSA

Personalised recommendations