Dynamic Programming Algorithm vs. Genetic Algorithm: Which is Faster?
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.
KeywordsGenetic Algorithm Execution Time Dynamic Programming Algorithm Execution Plan Query Optimization
Unable to display preview. Download preview PDF.
- 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.Bennett, K.; Ferris, M. C.; Ioannidis, Y. - A genetic algorithm for database query optimization, Tech. Report TR1004, Univ. Wisconsin, Madison, 1991Google Scholar
- 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.Goldberg, D. E. – Genetic Algorithms in Search, Optimization and Machine Learning, Addison Wesley, 1989.Google Scholar
- 5.Ioannidis, E. - Query Optimization, http://infolab.stanford.edu/~widom/cs346/ioannidis.pdf.
- 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.Lahiri, T. Genetic Optimization Techniques for Large Join Queries, in Proc. of the 3rd Genetic Programming Conf., 1998, pp.535-40.Google Scholar
- 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.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.Petković, D. – SQL Server 2008, A Beginner’s Guide, McGraw Hill, 2008.Google Scholar
- 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.PostgreSQL, http://www.postgresql.org
- 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.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