Advertisement

Dynamic Programming Algorithm vs. Genetic Algorithm: Which is Faster?

  • Dušan Petković
Conference paper

Abstract

The article compares two different approaches for the optimization problem of large join queries (LJQs). Almost all commercial database systems use a form of the dynamic programming algorithm to solve the ordering of join operations for large join queries, i.e. joins with more than dozen join operations. The property of the dynamic programming algorithm is that the execution time increases significantly in the case, where the number of join operations in a query is large. Genetic algorithms (GAs), as a data mining technique, have been shown as a promising technique in solving the ordering of join operations in LJQs. Using the existing implementation of GA, we compare the dynamic programming algorithm implemented in commercial database systems with the corresponding GA module. Our results show that the use of a genetic algorithm is a better solution for optimization of large join queries, i.e., that such a technique outperforms the implementations of the dynamic programming algorithm in conventional query optimization components for very large join queries.

Keywords

Genetic Algorithm Execution Time Dynamic Programming Algorithm Execution Plan Query Optimization 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Astrahan, M.M. et al. – Access Path Selection in a Relational Database Management System, in Proc. of the ACM SIGMOD Conf. on Management of Data, Boston, June 1979, pp.23-34.Google Scholar
  2. 2.
    Bennett, K.; Ferris, M. C.; Ioannidis, Y. - A genetic algorithm for database query optimization, Tech. Report TR1004, Univ. Wisconsin, Madison, 1991Google Scholar
  3. 3.
    Chaudhuri, S. – An Overview of Query Optimization in Relational Database Systems, Proceedings of the 17th ACM SIGACT-SIGMOD-SIGART symposium on Principles of database systems, Seattle, 1998.Google Scholar
  4. 4.
    Goldberg, D. E. – Genetic Algorithms in Search, Optimization and Machine Learning, Addison Wesley, 1989.Google Scholar
  5. 5.
    Ioannidis, E. - Query Optimization, http://infolab.stanford.edu/~widom/cs346/ioannidis.pdf.
  6. 6.
    Ioannidis, Y. E.; Kang, Y. C. - Randomized Algorithms for Optimizing Large Join Queries, Proc. of the 1990 ACM-SIGMOD Conference on the Management of Data. Atlantic City, NJ, 1990, pp. 312-321.Google Scholar
  7. 7.
    Lahiri, T. Genetic Optimization Techniques for Large Join Queries, in Proc. of the 3rd Genetic Programming Conf., 1998, pp.535-40.Google Scholar
  8. 8.
    Muntes-Mulero, V.; Aguilar-Saborit, J.; Zuzarte, C; Larriba-Pey, J. – SGO: A Sound Genetic Optimizer for Cyclic Query Graphs, in Alexandrov, V.N. et al. Proceedings of ICCS 2006, Part I, LNCS 3991, 2006, pp. 156-163.Google Scholar
  9. 9.
    Muntes-Mulero, V. et al. – Analyzing the Genetic Operations of an Evolutionary Query Optimizer, in Bell, D. and Hong. J. (Eds.), Proc. of BNCOD 2006, LNCS 4042, 2006.Google Scholar
  10. 10.
    Petković, D. – SQL Server 2008, A Beginner’s Guide, McGraw Hill, 2008.Google Scholar
  11. 11.
    Petković, D. - Comparison of Different Solutions for Solving the Optimization Problem of Large Join Queries, The Second Int. Conf. on Advances in Databases, Knowledge, and Data Applications, Les Menuires, France, 10.4.-14.4.2010.Google Scholar
  12. 12.
  13. 13.
    Stilger, M., Spiliopoulou, M. – Genetic Programming in Database Query Optimization, in Proc. of the 1st Genetic Programming Conference, 1996, pp. 388-93.Google Scholar
  14. 14.
    Steinbrunn, M., Moerkotte, G., Kemper, A. - Heuristic and randomized optimization for the join ordering problem. VLDB Journal, 6, 3 (Aug. 1997), Springer, New York, pp. 191-208Google Scholar
  15. 15.
    Whittley, D. – An Overview of Evolutionary Algorithms, Journal of Information and Software Technology 43, pp. 817-31, 2001.CrossRefGoogle Scholar

Copyright information

© Springer-Verlag London Limited 2011

Authors and Affiliations

  1. 1.University of Applied SciencesRosenheimGermany

Personalised recommendations