Abstract
The application of genetic programming strategies to query optimization has been proposed as a feasible way to solve the large join query problem. However, previous literature shows that the potentiality of evolutionary strategies has not been completely exploited in terms of convergence and quality of the returned query execution plans (QEP).
In this paper, we propose two alternatives to improve the performance of a genetic optimizer and the quality of the resulting QEPs. First, we present a new method called Weighted Election that proposes a criterion to choose the QEPs to be crossed and mutated during the optimization time. Second, we show that the use of heuristics in order to create the initial population benefits the speed of convergence and the quality of the results. Moreover, we show that the combination of both proposals outperforms previous randomized algorithms, in the best cases, by several orders of magnitude for very large join queries.
Research supported by the IBM Toronto Lab Centre for Advanced Studies and UPC Barcelona. The authors from DAMA-UPC want to thank Generalitat de Catalunya for its support through grant number GRE-00352 and Ministerio de Educación y Ciencia of Spain for its support through grant TIN2006-15536-C02-02.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
References
Banzhaf, W., Nordin, P., Keller, R.E., Francone, F.D.: Genetic Programming – An Introduction; On the Automatic Evolution of Computer Programs and its Applications. Morgan Kaufmann, San Francisco (Jan. 1998)
Bennett, K., Ferris, M.C., Ioannidis, Y.E.: A genetic algorithm for database query optimization. In: Belew, R., Booker, L. (eds.) Proceedings of the Fourth International Conference on Genetic Algorithms, pp. 400–407. Morgan Kaufmann, San Mateo (1991)
Chaudhuri, S., Dayal, U.: Data warehousing and OLAP for decision support. In: SIGMOD’97: Proceedings of the ACM SIGMOD international conference on Management of data, pp. 507–508 (1997)
Ioannidis, Y.E., Kang, Y.: Randomized algorithms for optimizing large join queries. In: SIGMOD ’90: Proc. of the 1990 ACM SIGMOD international conference on Management of data, pp. 312–321. ACM Press, New York (1990)
Ioannidis, Y.E., Wong, E.: Query optimization by simulated annealing. In: SIGMOD ’87: Proceedings of the 1987 ACM SIGMOD international conference on Management of data, pp. 9–22. ACM Press, New York (1987)
Kemper, A., Kossmann, D., Zeller, B.: Performance tuning for sap r/3. IEEE Data Eng. Bull. 22(2), 32–39 (1999)
Krishnamurthy, R., Boral, H., Zaniolo, C.: Optimization of nonrecursive queries. In: VLDB, pp. 128–137 (1986)
Muntés-Mulero, V., Aguilar-Saborit, J., Zuzarte, C., Larriba-Pey, J.-L.: CGO: A Sound Genetic Optimizer for Cyclic Query Graphs. In: Alexandrov, V.N., van Albada, G.D., Sloot, P.M.A., Dongarra, J. (eds.) ICCS 2006. LNCS, vol. 3991, pp. 156–163. Springer, Heidelberg (2006)
Muntés-Mulero, V., Aguilar-Saborit, J., Zuzarte, C., Markl, V., Larriba-Pey, J.-L.: Genetic evolution in query optimization: a complete analysis of a genetic optimizer. Technical Report UPC-DAC-RR-2005-21, Dept. d’Arqu. de Comp. Universitat Politecnica de Catalunya (2005), http://www.dama.upc.edu
Muntés-Mulero, V., Aguilar-Saborit, J., Zuzarte, C., Markl, V., Larriba-Pey, J.-L.: An inside analysis of a genetic-programming optimizer. In: Proc. of IDEAS ’06 (December 2006)
Muntés-Mulero, V., Pérez-Casany, M., Aguilar-Saborit, J., Zuzarte, C., Larriba-Pey, J.-L.: Parameterizing a Genetic Optimizer. In: Bressan, S., Küng, J., Wagner, R. (eds.) DEXA 2006. LNCS, vol. 4080, pp. 707–717. Springer, Heidelberg (2006)
PostgreSQL, http://www.postgresql.org/
Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., Price, T.G.: Access path selection in a relational database management system. In: Proceedings of the 1979 ACM SIGMOD international conference on Management of data, pp. 23–34. ACM Press, New York (1979)
Shekita, E.J., Young, H.C., Tan, K.-L.: Multi-join optimization for symmetric multiprocessors. In: Agrawal, R., Baker, S., Bell, D.A. (eds.) Proceedings of 19th International Conference on Very Large Data Bases, Dublin, Ireland, August 24-27, 1993, pp. 479–492. Morgan Kaufmann, San Francisco (1993)
Steinbrunn, M., Moerkotte, G., Kemper, A.: Heuristic and randomized optimization for the join ordering problem. VLDB Journal: Very Large Data Bases 6(3), 191–208 (1997)
Stillger, M., Spiliopoulou, M.: Genetic programming in database query optimization. In: Koza, J.R., Goldberg, D.E., Fogel, D.B., Riolo, R.L. (eds.) Genetic Programming 1996: Proceedings of the First Annual Conference, Stanford University, CA, USA, 28–31 July 1996, pp. 388–393. MIT Press, Cambridge (1996)
Swami, A.: Optimization of large join queries: combining heuristics and combinatorial techniques. In: SIGMOD ’89: Proceedings of the 1989 ACM SIGMOD international conference on Management of data, pp. 367–376. ACM Press, New York (1989)
Swami, A., Gupta, A.: Optimization of large join queries. In: SIGMOD ’88: Proceedings of the 1988 ACM SIGMOD international conference on Management of data, pp. 8–17. ACM Press, New York (1988)
Swami, A.N., Iyer, B.R.: A polynomial time algorithm for optimizing join queries. In: Proceedings of the Ninth International Conference on Data Engineering, Washington, DC, USA, pp. 345–354. IEEE Computer Society Press, Los Alamitos (1993)
Tao, Y., Zhu, Q., Zuzarte, C., Lau, W.: Optimizing large star-schema queries with snowflakes via heuristic-based query rewriting. In: CASCON ’03: Proceedings of the 2003 conference of the Centre for Advanced Studies on Collaborative research, pp. 279–293. IBM Press (2003)
Author information
Authors and Affiliations
Editor information
Rights and permissions
Copyright information
© 2007 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Muntés-Mulero, V., Lafón-Gracia, N., Aguilar-Saborit, J., Larriba-Pey, JL. (2007). Improving Quality and Convergence of Genetic Query Optimizers. In: Kotagiri, R., Krishna, P.R., Mohania, M., Nantajeewarawat, E. (eds) Advances in Databases: Concepts, Systems and Applications. DASFAA 2007. Lecture Notes in Computer Science, vol 4443. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-540-71703-4_3
Download citation
DOI: https://doi.org/10.1007/978-3-540-71703-4_3
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-71702-7
Online ISBN: 978-3-540-71703-4
eBook Packages: Computer ScienceComputer Science (R0)