Snakes on a Plan: Compiling Python Functions into Plain SQL Queries

"Move your computation close to the data" is decades-old advice that is hard to follow if your code exhibits complex control flow. The runtime of such applications suffers from a continual back and forth between database-external code execution and plan-based SQL evaluation. We demonstrate the ByePy compiler which translates entire Python functions with arbitrary control flow-including deeply nested iteration-into plain recursive SQL:1999 queries. The invocation of a ByePy-compiled function enters the database engine once to execute the plan of a single query. Computation does not get much closer to the data than this. The system rewards this translation effort from Python to SQL with runtime improvements of up to an order of magnitude.

[1]  Yuanchao Xu,et al.  UDF to SQL translation through compositional lazy inductive synthesis , 2021, Proc. ACM Program. Lang..

[2]  Torsten Grust,et al.  One WITH RECURSIVE is Worth Many GOTOs , 2021, SIGMOD Conference.

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

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

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

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

[7]  Alvin Cheung,et al.  Optimizing database-backed applications with query synthesis , 2013, PLDI.

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

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

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

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

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