Reorganizing queries with grouping

ABSTRACT

Language-integrated query has attracted much attention from researchers and engineers. It enables one to write a database query with high-level abstractions, which makes it possible to compose, iterate, and reuse queries. An important issue in language-integrated query is the N+1 query problem, and Cheney et al. proposed a program-transformation approach to solve it for a core language of Microsoft’s LINQ. In our previous work, we extended their language to grouping (GROUP BY in SQL) and aggregate functions, and showed that any term can be transformed to a single SQL query. It still has a problem in that the resulting queries may be unnecessarily large and inefficient.

This paper solves the problem. Our key idea is re-organization of queries with nested control structures. While our previous work decomposes grouping into finer primitives before transformation, the new algorithm fuses nested control structures after transformation, while keeping the absence of nested data structures. Our algorithm also eliminates correlated subqueries as much as possible, to obtain better performance. We have conducted performance measurements, which shows that our new algorithm reduces the size of generated queries and improves the performance for several examples.

References

1. James Cheney, Sam Lindley, and Philip Wadler. 2013. A practical theory of language-integrated query. In ACM SIGPLAN International Conference on Functional Programming, ICFP'13, Boston, MA, USA-September 25-27, 2013. 403-416. htps://doi.org/10.1145/2500365. 2500586
2. James Cheney, Sam Lindley, and Philip Wadler. 2014. Languageintegrated query using comprehension syntax: state of the art, open problems, and work in progress. Technical Report. htp://popl.mpisws.org/ 2014 /dcp2014/cheney.pdf, (Accessed on Oct 2020 ).
3. Ezra Cooper. 2009. The Script-Writer's Dream: How to Write Great SQL in Your Own Language, and Be Sure It Will Succeed. In Database Programming Languages-DBPL 2009, 12th International Symposium, Lyon, France, August 24, 2009. Proceedings. 36-51. htps://doi.org/10. 1007/978-3-642-03793-1_3
4. Ezra Cooper, Sam Lindley, Philip Wadler, and Jeremy Yallop. 2007. Links: Web Programming Without Tiers. In Formal Methods for Components and Objects, Frank S. de Boer, Marcello M. Bonsangue, Susanne Graf, and Willem-Paul de Roever (Eds.). Springer Berlin Heidelberg, Berlin, Heidelberg, 266-296.
5. Torsten Grust, Manuel Mayr, Jan Rittinger, and Tom Schreiber. 2009. FERRY: Database-Supported Program Execution. In Proceedings of the 2009 ACM SIGMOD International Conference on Management of Data (Providence, Rhode Island, USA) ( SIGMOD '09). Association for Computing Machinery, New York, NY, USA, 1063-1066. htps://doi. org/10.1145/1559845.1559982
6. Torsten Grust, Jan Rittinger, and Tom Schreiber. 2010. Avalanche-Safe LINQ Compilation. PVLDB 3, 1 ( 2010 ), 162-172. htps://doi.org/10. 14778/1920841.1920866
7. Oleg Kiselyov and Tatsuya Katsushima. 2017. Sound and Eficient Language-Integrated Query-Maintaining the ORDER. In Programming Languages and Systems-15th Asian Symposium, APLAS 2017, Suzhou, China, November 27-29, 2017, Proceedings. 364-383. htps: //doi.org/10.1007/978-3-319-71237-6_18
8. Erik Meijer, Brian Beckman, and Gavin M. Bierman. 2006. LINQ: reconciling object, relations and XML in the.NET framework. In Proceedings of the ACM SIGMOD International Conference on Management of Data, Chicago, Illinois, USA, June 27-29, 2006. 706. htps: //doi.org/10.1145/1142473.1142552
9. Atsushi Ohori and Katsuhiro Ueno. 2011. Making Standard ML a Practical Database Programming Language. In Proceedings of the 16th ACM SIGPLAN International Conference on Functional Programming (Tokyo, Japan) ( ICFP '11). Association for Computing Machinery, New York, NY, USA, 307-319. htps://doi.org/10.1145/2034773.2034815
10. Rui Okura and Yukiyoshi Kameyama. 2020. Language-Integrated Query with Nested Data Structures and Grouping. In Functional and Logic Programming, Keisuke Nakano and Konstantinos Sagonas (Eds.). Springer International Publishing, Cham, 139-158.
11. Wilmer Ricciotti and James Cheney. 2020. Strongly Normalizing Higher-Order Relational Queries. In 5th International Conference on Formal Structures for Computation and Deduction (FSCD 2020 ) (Leibniz International Proceedings in Informatics (LIPIcs), Vol. 167 ), Zena M. Ariola (Ed.). Schloss Dagstuhl-Leibniz-Zentrum für Informatik, Dagstuhl, Germany, 28 : 1-28 : 22. htps://doi.org/10.4230/LIPIcs.FSCD. 2020.28
12. Kenichi Suzuki, Oleg Kiselyov, and Yukiyoshi Kameyama. 2016. Finally, safely-extensible and eficient language-integrated query. In Proceedings of the 2016 ACM SIGPLAN Workshop on Partial Evaluation and Program Manipulation, PEPM 2016, St. Petersburg, FL, USA, January 20-22, 2016. 37-48. htps://doi.org/10.1145/2847538.2847542
13. Val Tannen, Peter Buneman, and Limsoon Wong. 1992. Naturally Embedded Query Languages. In Database Theory-ICDT' 92, 4th International Conference, Berlin, Germany, October 14-16, 1992, Proceedings. 140-154. htps://doi.org/10.1007/3-540-56039-4_38

Index Terms

• Software and its engineering

  • Software notations and tools

    • General programming languages

      • Language types

        • Functional languages

• Theory of computation

  • Theory and algorithms for application domains

    • Database theory

      • Database query processing and optimization (theory)