One WITH RECURSIVE is Worth Many GOTOs
Denis Hirn, Torsten Grust
Abstract
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.
Topics & Concepts
Computer scienceProgramming languageSQLPL/SQLStored procedureCompilerSQL/PSMAutocommitData definition languageUser-defined functionQuery by ExampleSQL injectionNull (SQL)Statement (logic)Language Integrated QueryDatabaseWorld Wide WebLawSearch engineWeb search queryPolitical scienceAdvanced Database Systems and QueriesScientific Computing and Data ManagementData Management and Algorithms