Engineering
Procedural Power, Set-speed: Inside e6data’s Froid-inspired UDF Engine
By Sweta Singh, Ruchir Raj
Picture the full expressive power of procedural code (variables, loops, even branching logic) living right inside your SQL, but without the notorious performance tax that usually comes with it. SQL User-Defined Functions (UDFs) were invented to give analysts that superpower, yet they often backfire when every call triggers a slow, row-by-row detour away from the query optimizer. At e6data, we decided that the trade-off was unacceptable, so we modified the rules. Inspired by Microsoft’s Froid framework and the wave of research that followed, our engine automatically inlines UDFs, fusing their logic back into the set-oriented query plan where it belongs. The result? Cleaner code, richer abstraction, and orders-of-magnitude faster execution, without asking developers to rewrite a single line. This post flags off a series where we’ll simplify UDFs, expose the bottlenecks, and then dive deep into the algorithms that enable e6data to make “procedural” feel as fast as “pure SQL.”
Why do UDFs Matter?
A SQL User-Defined Function, or UDF, is exactly what its name suggests: a function that you write and register inside the database to perform custom logic. Every major relational engine has supported UDFs for years, and cloud platforms such as Azure SQL Database log tens of millions of UDF definitions and billions of daily invocations. Clearly, developers love them, but there’s more to the story.
Why do Developers Reach for UDFs?
Benefit | What it buys you | Typical examples |
|---|---|---|
Modularity & Reuse | Write a rule once, call it everywhere. DRY for SQL. | currency_convert(), apply_discount() |
Imperative Expressiveness | Loops, conditionals, nested calls; perfect for rules that don’t fit a single SELECT. | tiered-tax calculations, complex eligibility checks |
Maintainability & Readability | A named function ( calculate_tax(…) ) beats a 200-line inline expression. | Cleaner code reviews, easier onboarding |
Data Proximity | Logic runs inside the engine; no ETL, no external app server. | Real-time scoring, in-database cleansing |
With benefits like these, UDFs have become a staple of real-world SQL development. Seasoned DBAs, however, know the catch: performance pain. To see why, we first need to distinguish the two broad flavors of UDFs.
What are the Differences between Inline and Multi-Statement UDFs?
Despite their elegance and convenience, UDFs aren’t all built alike. The moment a developer moves from a tidy one-liner to a block with variables and loops, the database stops treating the function as part of the set-based plan and starts executing it row-by-row. That line in the sand, between inline and multi-statement UDFs, is exactly where performance fortunes diverge. Before we dive into how e6data erases that penalty, let’s clarify what distinguishes these two flavors and why the choice can make or break a query’s speed.
Type | What it Looks Like | How the Engine Usually Treats it | Performance Profile |
|---|---|---|---|
Inline (single-statement) | A one-liner that returns the result of a single query or expression. | Expanded (“macro-inlined”) directly into the outer query. | Fast: the optimizer sees and optimizes it like normal SQL. |
Multi-Statement | A BEGIN … END block with variables, multiple queries, loops, or nested function calls. | Executed as a separate routine, often once per row. | Slow: imperative control flow defeats set-based optimization. |
If your function is a simple expression, life is good. The database can slide it into the main plan and keep everything set-oriented. The moment you add loops, branches, or multiple statements, you cross into multi-statement territory. That’s where the pain starts.
Why do Traditional UDFs Slow You Down?
Context-Switch Overhead
Every invocation bounces the engine from set-oriented execution to a procedural mini-interpreter and back. Thousands of hops = thousands of stalls.Row-by-Row (RBAR) Execution
A multi-statement UDF inside a SELECT list often fires once per output row. A 100 K-row result becomes 100 K miniature procedures, usually single-threaded.Optimizer Blindness
The planner can’t peek inside the function, so it can’t reorder joins, push predicates, or parallelize. Lacking estimates, it often chooses the safest and slowest plan: evaluate the UDF verbatim for every row.
In combination, these flaws can throttle queries by 10x, 100x, even 1000x compared with set-based SQL.
Let’s understand how UDF works by taking an example business rule wrapped in a multi-statement UDF
A seemingly innocent payroll query that calls the UDF:
Under the hood, vanilla lakehouse engines still execute it like this, and the pain is familiar
Step | Phase | What happens | Context |
|---|---|---|---|
1 | Scan Employee | Read first row (emp_id = 42). | Set‑oriented |
2 | Context switch to UDF | Jump into procedure interpreter, allocate locals. | Procedural / row‑at‑a‑time |
3 | Loop over slabs | Run 1–3 assignment statements for each slab. | Procedural |
4 | Return tax | Single value bubbled back to outer query. | Procedural |
5 | Switch back | Resume main query, output row. | Set‑oriented |
6 | Repeat 1 - 5 | …for every remaining employee row. |
The result? 50,000 employees = 50,000 miniature procedures plus tens of thousands of context switches. This is a textbook RBAR collapse.
As with the original loyalty‑tier example, the optimizer is blind to slab predicates, can’t push filters, and usually falls back to single‑threaded nested loops, i.e., the classic “avoid procedural UDFs in critical paths” warning.
Together, these issues can turn a neat, reusable abstraction into a 10x, 100x, or even 1000x slowdown. That’s why many DBAs warn, “Avoid UDFs in critical paths.”
Databricks and Snowflake do expose “SQL UDFs,” but only in the most limited, macro-like form: the body must be a single expression or query (no variables, no loops, no multi-statement logic) - CREATE FUNCTION (SQL and Python) | Databricks Documentation and Scalar SQL UDFs | Snowflake Documentation. As a result, developers quickly bump into those guardrails and fall back to external Python/Java UDFs or stored procedures, forfeiting optimizer insight and lakehouse performance.
e6data takes a different route: We are among the first lakehouse engines to bring full Froid-style inlining of multi-statement SQL UDFs into the platform. Instead of forcing users to shrink their logic (or move it out of SQL), we melt the procedural wrapper into a single relational plan the optimizer can chew on. So, no rewrites, no compromises.
For years, that warning felt like the end of the conversation: either abandon multi-statement UDFs or accept the drag they impose. But in database research circles, a quiet revolution was brewing. Starting with Microsoft’s landmark Froid paper, engineers showed that the real fix isn’t to delete UDFs, but to dissolve the procedural shell and fold its logic back into the relational plan. In other words, let the optimizer see everything, then do what it does best. We embraced and extended that idea inside our e6data engine. Instead of telling users to rewrite code, we rewrite the execution model: our inliner automatically transforms the example function above into a single, set-oriented expression the optimizer can streamline and parallelize at will.
Now the optimizer “sees” everything: parallelize the join, and eliminate every context switch. Execution time plummets, while developers keep writing clear, modular UDFs.
In our next posts, we’ll walk through how research breakthroughs like Microsoft’s Froid taught the industry to inline multi-statement UDFs (transforming them back into set-oriented plans) and how we built that capability natively into our e6data engine.
Stay tuned! The deep dive into the inlining algorithm is where the real fun begins!
Resource
Froid: Optimization of Imperative Programs in a Relational Database