Overview
User-defined functions (UDFs) are procedural functions (written in PL/SQL, Python) that users call from SQL queries. However, queries with UDFs are notoriously hard to optimize as they mix declarative SQL and procedural UDF code. As a result, SQL and UDFs have had a problematic relationship. In SQL Server 2019, Microsoft shipped UDF inlining, a feature that eliminates all UDF code by translating entire UDFs to SQL. Yet, we observe that inlining often leads to sub-optimal performance.
In this session recording from HTAP Summit 2024, we propose a new optimization technique, UDF outlining, that intentionally hides pieces of a UDF from the query optimizer, resulting in significantly simpler and faster queries. Are SQL and UDFs finally ready to be together?