Abstract
Many SQL queries with aggregated subqueries exhibit redundancy (overlap in FROM and WHERE clauses). We propose a method, called the for-loop, to optimize such queries by ensuring that redundant computations are done only once. We specify a procedure to build a query plan implementing our method, give an example of its use and argue that it offers performance advantages over traditional approaches.
This research was sponsored by NSF under grant IIS-0091928.
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
Badia, A., Niehues, M.: Optimization of sequences of relational queries in decision-support environments. In: Mohania, M., Tjoa, A.M. (eds.) DaWaK 1999. LNCS, vol. 1676, pp. 126–131. Springer, Heidelberg (1999)
Cao, B., Badia, A.: Subquery Rewriting for Optimization of SQL Queries (submitted for publication)
Dayal, U.: Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers. In: Proceedings of the VLDB Conference (1987)
Fegaras, L., Maier, D.: Optimizing Queries Using an Effective Calculus. ACM TODSÂ 25(4) (2000)
Freytag, J., Goodman, N.: On the Translation of Relational Queries into Iterative Programs. ACM Transactions on Database Systems 14(1) (March 1989)
Ganski, R., Wong, H.: Optimization of Nested SQL Queries Revisited. In: Proceedings of the ACM SIGMOD Conference (1987)
Goel, P., Iyer, B.: SQL Query Optimization: Reordering for a General Class of Queries. In: Proceedings of the 1996 ACM SIGMOD Conference (1996)
Kim, W.: On Optimizing an SQL-Like Nested Query. ACM Transactions On Database Systems 7(3) (September 1982)
Lieuwen, D., DeWitt, D.: A Transformation-Based Approach to Optimizing Loops in database Programming Languages. In: Proceedings of the ACM SIGMOD Conference (1992)
Lu, H., Chan, H.C., Wei, K.K.: A Survey on Usage of SQL. SIGMOD Record (1993)
Muralikrishna, M.: Improving Unnesting Algorithms for Join Aggregate Queries in SQL. In: Proceedings of the VLDB Conference (1992)
Park, J., Segev, A.: Using common subexpressions to optimize multiple queries. In: Proceedings of the 1988 IEEE CS ICDE (1988)
Ross, K., Rao, J.: Reusing Invariants: A New Strategy for Correlated Queries. In: Proceedings of the ACM SIGMOD Conference (1998)
Rao, J., Lindsay, B., Lohman, G., Pirahesh, H., Simmen, D.: Using EELs, a Practical Approach to Outerjoin and Antijoin Reordering. In: Proceedings of ICDE (2001)
Seshadri, P., Pirahesh, H., Cliff Leung, T.Y.: Complex Query Decorrelation. In: Proceedings of ICDE (1996)
Seshadri, P., Hellerstein, J.M., Pirahesh, H., Cliff Leung, T.Y., Ramakrishnan, R., Srivastava, D., Stuckey, P.J., Sudarshan, S.: Cost-Based Optimization for Magic: Algebra and Implementation. In: Proceedings of the SIGMOD Conference (1996)
Mumick, I.S., Pirahesh, H.: Implementation of Magic-sets in a Relational Database System. In: Proceedings of the SIGMOD Conference (1994)
TPC-H Benchmark, TPC Council, http://www.tpc.org/home.page.html
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2003 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Badia, A., Anand, D. (2003). Fighting Redundancy in SQL. In: Kambayashi, Y., Mohania, M., Wöß, W. (eds) Data Warehousing and Knowledge Discovery. DaWaK 2003. Lecture Notes in Computer Science, vol 2737. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-540-45228-7_40
Download citation
DOI: https://doi.org/10.1007/978-3-540-45228-7_40
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-40807-9
Online ISBN: 978-3-540-45228-7
eBook Packages: Springer Book Archive