One WITH RECURSIVE is Worth Many GOTOs

PL/SQL integrates an imperative statement-by-statement style of programming with the plan-based evaluation of SQL queries. The disparity of both leads to friction at runtime, slowing PL/SQL execution down significantly. This work describes a compiler from PL/SQL UDFs to plain SQL queries. Post-compilation, evaluation entirely happens on the SQL side of the fence. With the friction gone, we observe execution times to improve by about a factor of 2, even for complex UDFs. The compiler builds on techniques long established by the programming language community. In particular, it uses trampolined style to compile arbitrarily nested iterative control flow in PL/SQL into SQL's recursive common table expressions.

[1]  Mark N. Wegman,et al.  Efficiently computing static single assignment form and the control dependence graph , 1991, TOPL.

[2]  Jack Chen,et al.  The MemSQL Query Optimizer: A modern optimizer for real-time analytics in a distributed database , 2016, Proc. VLDB Endow..

[3]  Goetz Graefe,et al.  Volcano - An Extensible and Parallel Query Evaluation System , 1994, IEEE Trans. Knowl. Data Eng..

[4]  Manuel M. T. Chakravarty,et al.  A Functional Perspective on SSA Optimisation Algorithms , 2004, COCV@ETAPS.

[5]  Michael Stonebraker,et al.  The POSTGRES Data Model , 1987, Research Foundations in Object-Oriented and Semantic Database Systems.

[6]  Thomas Johnsson,et al.  Lambda Lifting: Treansforming Programs to Recursive Equations , 1985, FPCA.

[7]  David C. Cooper,et al.  Böhm and Jacopini's reduction of flow charts , 1967, CACM.

[8]  César A. Galindo-Legaria,et al.  Orthogonal optimization of subqueries and aggregation , 2001, SIGMOD '01.

[9]  J. J. Garcia-Luna-Aceves,et al.  A path-finding algorithm for loop-free routing , 1997, TNET.

[10]  Simon L. Peyton Jones,et al.  A history of Haskell: being lazy with class , 2007, HOPL.

[11]  Jun Yang,et al.  Data Management in Machine Learning Systems , 2019, Data Management in Machine Learning Systems.

[12]  Torsten Grust,et al.  PL/SQL Without the PL , 2020, SIGMOD Conference.

[13]  Joseph K. Bradley,et al.  Spark SQL: Relational Data Processing in Spark , 2015, SIGMOD Conference.

[14]  Amr Sabry,et al.  Reasoning about programs in continuation-passing style , 1992, LFP '92.

[15]  Mitchell Wand,et al.  Trampolined style , 1999, ICFP '99.

[16]  Anas N. Al-Rabadi,et al.  A comparison of modified reconstructability analysis and Ashenhurst‐Curtis decomposition of Boolean functions , 2004 .

[17]  Kwanghyun Park,et al.  Froid: Optimization of Imperative Programs in a Relational Database , 2017, Proc. VLDB Endow..

[18]  Uday Bondhugula,et al.  MLIR: A Compiler Infrastructure for the End of Moore's Law , 2020, ArXiv.

[19]  Karthik Ramachandra,et al.  Aggify: Lifting the Curse of Cursor Loops using Custom Aggregates , 2020, SIGMOD Conference.

[20]  Joe D. Warren,et al.  The program dependence graph and its use in optimization , 1987, TOPL.

[21]  Torsten Grust,et al.  The Flatter, the Better: Query Compilation Based on the Flattening Transformation , 2015, SIGMOD Conference.

[22]  François Bancilhon,et al.  Naive Evaluation of Recursively Defined Relations , 1986, On Knowledge Base Management Systems.

[23]  Jim Melton,et al.  SQL: 1999, formerly known as SQL3 , 1999, SGMD.

[24]  S. Sudarshan,et al.  Rewriting procedures for batched bindings , 2008, Proc. VLDB Endow..

[25]  Stephan Günnemann,et al.  SQL- and Operator-centric Data Analytics in Relational Main-Memory Databases , 2017, EDBT.

[26]  Wen-mei W. Hwu,et al.  Inline function expansion for compiling C programs , 1989, PLDI '89.

[27]  Suresh Jagannathan,et al.  Flow-directed inlining , 1996, PLDI '96.

[28]  Amr Sabry,et al.  The essence of compiling with continuations , 1993, PLDI '93.

[29]  Torsten Grust,et al.  Compiling PL/SQL Away , 2019, CIDR.

[30]  S. Sudarshan,et al.  Extracting Equivalent SQL from Imperative Code in Database Applications , 2016, SIGMOD Conference.

[31]  Christopher Ré,et al.  Towards a unified architecture for in-RDBMS analytics , 2012, SIGMOD Conference.

[32]  R. Kent Dybvig,et al.  Fast and Effective Procedure Inlining , 1997, SAS.

[33]  Thomas Neumann,et al.  Efficiently Compiling Efficient Query Plans for Modern Hardware , 2011, Proc. VLDB Endow..

[34]  Kwanghyun Park,et al.  BlackMagic: Automatic Inlining of Scalar UDFs into SQL Queries with Froid , 2019, Proc. VLDB Endow..

[35]  Chris Jermaine,et al.  Declarative Recursive Computation on an RDBMS, or, Why You Should Use a Database For Distributed Machine Learning , 2019, ArXiv.

[36]  Timos K. Sellis,et al.  Multiple-query optimization , 1988, TODS.

[37]  Torsten Grust,et al.  Avalanche-safe LINQ compilation , 2010, Proc. VLDB Endow..

[38]  Viktor Leis,et al.  Adaptive Execution of Compiled Queries , 2018, 2018 IEEE 34th International Conference on Data Engineering (ICDE).