One WITH RECURSIVE Is Worth Many GOTOs
Proceedings of the 40th ACM SIGMOD Int’l Conference on Management of Data (SIGMOD 2021), Xi’an, Shaanxi, China, June 2021.
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.
- SIGMOD 2021 version of the paper (PDF)
- Extended version of the paper (PDF)
- GitHub repository with sample PL/SQL UDFs and their compiled plain SQL variants (ready to run on PostgreSQL v11+)