One-sentence Summary

Carnegie Mellon University proposes PRISM, a UDF outlining technique that intentionally hides portions of user-defined functions from the SQL optimizer to enable selective inlining, achieving up to $298.73\times$298.73× speedup over conventional full inlining through more effective unnesting, improved data skipping, and avoidance of unnecessary joins.

Key Contributions

• This paper introduces UDF outlining, which deliberately hides selected pieces of a user-defined function from the optimizer and inlines only fragments that help optimization, replacing whole-UDF inlining with simpler SQL subqueries and more efficient plans.

• PRISM implements UDF outlining with complementary optimizations that generate LATERAL-free subqueries, enable more effective unnesting, improve data skipping, and avoid unnecessary joins in DuckDB and SQL Server queries.

• Experiments show performance gains over conventional UDF inlining, averaging 1.29x for DuckDB and 298.73x for SQL Server, with unnesting yielding over 1000x, data skipping about 10x, and join avoidance 1.02x to 1.62x.

Introduction

Application developers often extend SQL with user-defined functions (UDFs), but these functions are opaque to database query optimizers, which can cause UDF-heavy queries to run much slower than equivalent SQL queries. Prior optimization methods such as compilation, batching, and UDF inlining improve performance in some cases, yet they have limitations: compilation does not fix a poor query plan, batching can require expensive materialization, and inlining can create complex subqueries with LATERAL joins that are hard to unnest, slow to execute, and resistant to data-skipping. The authors introduce PRISM, a UDF optimization framework that deconstructs UDFs at registration time and selectively inlines only useful fragments while outlining others to reduce query complexity. The authors also use UDF outlining, predicate hoisting, and complementary restructuring optimizations to expose better plans and data-skipping opportunities to DuckDB and Microsoft SQL Server, achieving large speedups on UDF-centric workloads.

Dataset

• The authors use two evaluation workloads centered on SQL user-defined functions.
• SQL ProcBench is a Microsoft benchmark released in 2021, modeled after real-world UDF workloads on Azure SQL Server and built on TPC-DS.
• ProcBench contains 24 queries that invoke scalar UDFs.
• The authors run ProcBench at scale factor 10, with an approximate data size of 10 GB.
• They use 17 of the 24 queries, excluding queries that use table-valued functions or UDFs invoked from stored procedures.
• They also skip queries Q8 and Q14 on SQL Server because these queries use cursor-loop UDFs that could not be compiled in the Linux version of SQL Server 2022.
• TPC-H FROID is a FROID variant of TPC-H that manually rewrites 12 benchmark queries to use scalar UDFs.
• The TPC-H FROID UDFs are simpler than ProcBench UDFs, using purely procedural or relational logic rather than mixed procedural and SELECT statements.
• The FROID UDFs factor expressions into reusable functions instead of embedding complex business logic.
• The FROID queries invoke UDFs in WHERE clauses, which blocks data skipping optimizations unless the DBMS applies predicate hoisting.
• The authors use these datasets as experimental workloads for evaluating query execution and optimization behavior, not as a training dataset.

Method

PRISM is a compile-time optimization framework for user-defined functions that decomposes a UDF into relational pieces suited for inlining and procedural pieces suited for outlining. Instead of rewriting the full function into a complex SQL subquery at query time, the system processes the UDF when it is registered through CREATE FUNCTION. The framework parses the function, binds expressions and table references against the catalog, translates the code into an intermediate representation, and applies compiler-style optimizations. The goal is to expose only the most optimizer-friendly SQL fragments to the query planner while compiling the remaining procedural logic into opaque, high-performance function calls. Refer to the framework diagram.

The internal representation used by PRISM combines static single assignment form with a hierarchy of program regions. The UDF is first represented as a control flow graph in SSA form, where each variable is assigned exactly once and versions such as $x_1$x1​, $x_2$x2​, and $x_3$x3​ are created for different definitions. At control flow join points, $\phi$ϕ functions such as $x_3 = \phi(x_1, x_2)$x3​=ϕ(x1​,x2​) record the value that should flow from each predecessor. The same program is also organized as a program structure tree of single-entry single-exit regions, including leaf regions, conditional regions, loop regions, and sequential regions. Embedded SELECT statements are placed in dedicated basic blocks so that procedural code can be separated from relational code during optimization. Refer to the intermediate representation.

The first major optimization is query motion. Many UDFs mix procedural control flow with embedded relational queries, for example placing a SELECT statement inside an IF/ELSE block or a loop. This coupling prevents the optimizer from outlining the procedural code cleanly. PRISM repeatedly hoists eligible SELECT statements as high as possible in the region tree. If the statement’s referenced variables are not defined in the current region, the statement is moved to the parent region. When the statement crosses a conditional region, PRISM appends the guarding condition to the query, ensuring that the statement executes only when the original branch condition holds. This step separates relational code, which should be inlined, from procedural code, which can be outlined. Refer to the query motion diagram.

After query motion, PRISM performs region-based UDF outlining. The system recursively traverses the region tree from the top down and extracts the largest procedural regions that do not contain embedded SELECT statements. Regions containing SELECT statements remain visible to the database optimizer, while SELECT-free regions are removed from the UDF and compiled as separate functions. Liveness analysis determines the input variables and output variables of the extracted region. The extracted code is then transpiled into machine code, for example through C++ generation and a compiler toolchain, and dynamically linked into the database system. The original code is replaced by an opaque function call, which reduces the amount of UDF logic exposed to the optimizer. Refer to the region-based outlining diagram.

The next step is instruction elimination. Even after outlining, the remaining UDF may contain several assignment instructions. If these instructions are later inlined into SQL, each assignment can become a LATERAL join, making the generated query difficult to unnest and slow to execute. PRISM uses the single-definition property of SSA form to collapse the function. For an instruction such as $y = f(x)$y=f(x), the system can replace every use of $y$y with $f(x)$f(x) and remove the original assignment. When a variable is used once, this transformation is usually safe and beneficial. When a variable has multiple uses, PRISM considers the cost of duplication and the capabilities of the target database system. Non-query expressions may be duplicated because the optimizer can often eliminate redundant work through common-subexpression elimination, while query duplication is applied more selectively. The result is a highly simplified UDF, often reduced to a single RETURN instruction. Refer to the instruction elimination diagram.

PRISM further reduces the cost of UDF calls through subquery elision. Once the UDF has been simplified to a single return expression, the system can replace the original UDF invocation directly with that expression in the calling query. This bypasses unnecessary subquery generation and avoids introducing additional relational structures that the optimizer would need to process. For UDFs invoked in a WHERE clause, PRISM also performs predicate hoisting. The system analyzes all execution paths that reach a RETURN statement, converts each path into a conjunctive boolean condition, and combines paths through disjunction. Branch conditions along a path are conjoined with a generated predicate that compares the return value against an additional parameter, enabling the database optimizer to push the predicate deeper into the plan and use data-skipping mechanisms. Loop-containing predicates are generally ignored because they cannot be directly represented as simple filters.

The implementation treats the UDF registration step as a compilation phase. The system intercepts CREATE FUNCTION, parses the procedural body, uses database binder components to resolve expressions and aliases, and builds the SSA-based region representation. Outlined fragments are emitted as compiled code, while inlined pieces are materialized as SQL expressions or query fragments. This architecture combines compilation, inlining, outlining, and predicate rewrites into a unified pipeline that produces simpler relational queries and faster function execution.

Experiment

The experiments evaluate PRISM integrated into DuckDB and SQL Server on ProcBench and a TPC-H FROID workload, comparing PRISM-optimized UDFs with DBMS inlining under single-threaded, multi-threaded, and ablation settings. They show that PRISM provides substantial qualitative benefits when inlining produces complex procedural plans, recursive CTEs, LATERAL joins, or opaque predicates, because its simplifications and outlining enable more efficient joins, set-oriented unnesting, compiled execution, and data-skipping optimizations. The results also reveal trade-offs and DBMS-specific constraints: DuckDB retains parallel gains, SQL Server's benefit can be limited by parallel execution limitations and column-store pushdown behavior, and query duplication is mainly valuable when it unlocks more efficient unnesting.

PRISM delivers substantial single-threaded speedups on UDF-centric queries for DuckDB and SQL Server across ProcBench and TPC-H, with improvements often spanning several orders of magnitude. SQL Server shows large average gains from unnested, join-based UDF evaluation, while DuckDB shows modest average ProcBench gains but large maximum speedups from eliminating inefficient plan constructs. On TPC-H, both systems benefit from better predicate exposure and data skipping, though maximum values are driven by outlier queries. SQL Server averages higher speedups than DuckDB for both benchmarks DuckDB gains mostly from eliminating LATERAL joins, subqueries, and cross-product plans TPC-H improvements come from exposing selective predicates and enabling data skipping

DuckDB is shown to unnest all ProcBench subqueries for both baseline inlining and PRISM, so its performance gains are not explained by a failure to unnest. SQL Server is much more dependent on PRISM because it cannot unnest many subqueries produced by straightforward inlining, whereas PRISM's simplified UDFs enable unnesting for a larger set of queries. PRISM's simplified UDFs make more SQL Server queries amenable to subquery unnesting than baseline inlining. DuckDB already unnests all listed ProcBench queries in the baseline and PRISM settings. PRISM reduces the need for SQL Server to evaluate subqueries through inefficient plan forms by enabling join-based execution.

Compilation of the outlined function makes the single-threaded query much faster than the inlined version. The improvement is tied to lower instruction and branch counts, and especially to far fewer last-level cache misses. The larger cache savings come from avoiding SQL structures used to emulate loops, which require extra working tables and join work. Compiled execution has a large execution-time reduction. Instruction and branch activity decrease sharply after compilation. Last-level cache misses decrease more than other counters, reducing stalls from intermediate SQL structures.

For three ProcBench queries on SQL Server, enabling query duplication made execution slower than disabling it. The slowdown was modest and occurred because duplication did not change whether SQL Server unnested these queries. The optimization is still kept enabled by default because unnesting can produce much larger performance gains than the duplication overhead. Query duplication increased execution time for all three tested ProcBench queries. Disabling query duplication produced the better normalized execution times. The duplication overhead was small compared with the possible gains from unnesting.

For the tested ProcBench queries on SQL Server, PRISM's query duplication does not change whether the DBMS unnests the generated subqueries. Q18 is not fully unnested with duplication enabled or disabled, while Q20b1 and Q20b2 are unnested in both cases. Since duplication can add overhead without improving unnesting for these queries, disabling it avoids performance regressions. Query duplication has no observable effect on SQL Server unnesting for the tested ProcBench queries. Disabling duplication keeps performance at least as good as inlining for the tested queries.

The experiments evaluate PRISM’s effects on UDF-centric queries in DuckDB and SQL Server using ProcBench and TPC-H, showing substantial single-threaded speedups from simplified UDF compilation and join-based execution. SQL Server benefits especially because PRISM enables broader subquery unnesting and avoids inefficient subquery plans, while DuckDB gains primarily by removing costly LATERAL, subquery, and cross-product constructs and by exposing selective predicates and data skipping. The results also show that SQL structures used to emulate loops increase instruction, branch, and cache-miss overhead, and that query duplication can cause modest regressions when it does not improve unnesting.