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.
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
CREATE OR REPLACE FUNCTION calc_progressive_tax(income INTEGER, -- taxable incomeslab_limits INTEGER ARRAY, -- upper bound of each slabslab_rates NUMERIC(5,2)ARRAY -- rate(%)foreach slab)RETURNS NUMERIC(18,2)LANGUAGE SQLAS$$DECLAREidx INTEGER;remaining INTEGER;bracketAmt INTEGER;tax NUMERIC(18,2);BEGIN
-- Guard‑railsIF(income IS NULL OR income <= 0)THENRETURN 0;END IF;
remaining := income;
tax := 0;
idx := 1;
-- Walk the slabs one‑by‑oneWHILE(idx <= CARDINALITY(slab_limits))DOBEGIN
-- all income consumed → early exitIF(remaining <= 0)THENBREAK;END IF;
-- amount taxedinthisslab
bracketAmt := LEAST(remaining,slab_limits[idx]);
-- add slab’s contribution
tax := tax + bracketAmt * slab_rates[idx] / 100;
-- move to next slab
remaining := remaining - bracketAmt;
idx := idx + 1;END;END WHILE;RETURN tax;END$$
CREATE OR REPLACE FUNCTION calc_progressive_tax(income INTEGER, -- taxable incomeslab_limits INTEGER ARRAY, -- upper bound of each slabslab_rates NUMERIC(5,2)ARRAY -- rate(%)foreach slab)RETURNS NUMERIC(18,2)LANGUAGE SQLAS$$DECLAREidx INTEGER;remaining INTEGER;bracketAmt INTEGER;tax NUMERIC(18,2);BEGIN
-- Guard‑railsIF(income IS NULL OR income <= 0)THENRETURN 0;END IF;
remaining := income;
tax := 0;
idx := 1;
-- Walk the slabs one‑by‑oneWHILE(idx <= CARDINALITY(slab_limits))DOBEGIN
-- all income consumed → early exitIF(remaining <= 0)THENBREAK;END IF;
-- amount taxedinthisslab
bracketAmt := LEAST(remaining,slab_limits[idx]);
-- add slab’s contribution
tax := tax + bracketAmt * slab_rates[idx] / 100;
-- move to next slab
remaining := remaining - bracketAmt;
idx := idx + 1;END;END WHILE;RETURN tax;END$$
CREATE OR REPLACE FUNCTION calc_progressive_tax(income INTEGER, -- taxable incomeslab_limits INTEGER ARRAY, -- upper bound of each slabslab_rates NUMERIC(5,2)ARRAY -- rate(%)foreach slab)RETURNS NUMERIC(18,2)LANGUAGE SQLAS$$DECLAREidx INTEGER;remaining INTEGER;bracketAmt INTEGER;tax NUMERIC(18,2);BEGIN
-- Guard‑railsIF(income IS NULL OR income <= 0)THENRETURN 0;END IF;
remaining := income;
tax := 0;
idx := 1;
-- Walk the slabs one‑by‑oneWHILE(idx <= CARDINALITY(slab_limits))DOBEGIN
-- all income consumed → early exitIF(remaining <= 0)THENBREAK;END IF;
-- amount taxedinthisslab
bracketAmt := LEAST(remaining,slab_limits[idx]);
-- add slab’s contribution
tax := tax + bracketAmt * slab_rates[idx] / 100;
-- move to next slab
remaining := remaining - bracketAmt;
idx := idx + 1;END;END WHILE;RETURN tax;END$$
CREATE OR REPLACE FUNCTION calc_progressive_tax(income INTEGER, -- taxable incomeslab_limits INTEGER ARRAY, -- upper bound of each slabslab_rates NUMERIC(5,2)ARRAY -- rate(%)foreach slab)RETURNS NUMERIC(18,2)LANGUAGE SQLAS$$DECLAREidx INTEGER;remaining INTEGER;bracketAmt INTEGER;tax NUMERIC(18,2);BEGIN
-- Guard‑railsIF(income IS NULL OR income <= 0)THENRETURN 0;END IF;
remaining := income;
tax := 0;
idx := 1;
-- Walk the slabs one‑by‑oneWHILE(idx <= CARDINALITY(slab_limits))DOBEGIN
-- all income consumed → early exitIF(remaining <= 0)THENBREAK;END IF;
-- amount taxedinthisslab
bracketAmt := LEAST(remaining,slab_limits[idx]);
-- add slab’s contribution
tax := tax + bracketAmt * slab_rates[idx] / 100;
-- move to next slab
remaining := remaining - bracketAmt;
idx := idx + 1;END;END WHILE;RETURN tax;END$$
A seemingly innocent payroll query that calls the UDF:
SELECTe.emp_id,e.name,calc_progressive_tax(e.salary,ARRAY[250000,500000,1000000],ARRAY[5,20,30])AS tax_dueFROM employee AS eWHERE e.fiscal_year = 2025;
SELECTe.emp_id,e.name,calc_progressive_tax(e.salary,ARRAY[250000,500000,1000000],ARRAY[5,20,30])AS tax_dueFROM employee AS eWHERE e.fiscal_year = 2025;
SELECTe.emp_id,e.name,calc_progressive_tax(e.salary,ARRAY[250000,500000,1000000],ARRAY[5,20,30])AS tax_dueFROM employee AS eWHERE e.fiscal_year = 2025;
SELECTe.emp_id,e.name,calc_progressive_tax(e.salary,ARRAY[250000,500000,1000000],ARRAY[5,20,30])AS tax_dueFROM employee AS eWHERE e.fiscal_year = 2025;
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!