{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2026,4,23]],"date-time":"2026-04-23T18:45:46Z","timestamp":1776969946514,"version":"3.51.4"},"reference-count":50,"publisher":"Association for Computing Machinery (ACM)","issue":"1","content-domain":{"domain":["dl.acm.org"],"crossmark-restriction":true},"short-container-title":["SIGMOD Rec."],"published-print":{"date-parts":[[2026,4,23]]},"abstract":"<jats:p>Although user-defined functions (UDFs) are a popular way to augment SQL's declarative approach with procedural code, the mismatch between programming paradigms creates a fundamental optimization challenge. UDF inlining automatically removes all UDF calls by replacing them with equivalent SQL subqueries. Although inlining leaves queries entirely in SQL (resulting in large performance gains), we observe that inlining the entire UDF often leads to suboptimal performance. A better approach is to analyze the UDF, deconstruct it into smaller pieces, and inline only the pieces that help query optimization. To achieve this, we propose UDF outlining, a technique to intentionally hide pieces of a UDF from the optimizer, resulting in simpler UDFs and significantly faster query plans. Our implementation (PRISM) demonstrates that UDF outlining improves performance over conventional inlining (on average 1.29\u00d7 speedup for DuckDB and 298.73\u00d7 for SQL Server) through a combination of more effective unnesting, improved data skipping, and by avoiding unnecessary joins.<\/jats:p>","DOI":"10.1145\/3810900.3810914","type":"journal-article","created":{"date-parts":[[2026,4,23]],"date-time":"2026-04-23T18:16:38Z","timestamp":1776968198000},"page":"74-83","update-policy":"https:\/\/doi.org\/10.1145\/crossmark-policy","source":"Crossref","is-referenced-by-count":0,"title":["Partial UDF Inlining"],"prefix":"10.1145","volume":"55","author":[{"given":"Samuel","family":"Arch","sequence":"first","affiliation":[{"name":"Carnegie Mellon University, Pittsburgh, PA, USA"}],"role":[{"role":"author","vocabulary":"crossref"}]},{"given":"Yuchen","family":"Liu","sequence":"additional","affiliation":[{"name":"Carnegie Mellon University, Pittsburgh, PA, USA"}],"role":[{"role":"author","vocabulary":"crossref"}]},{"given":"Todd C.","family":"Mowry","sequence":"additional","affiliation":[{"name":"Carnegie Mellon University, Pittsburgh, PA, USA"}],"role":[{"role":"author","vocabulary":"crossref"}]},{"given":"Jignesh M.","family":"Pate","sequence":"additional","affiliation":[{"name":"Carnegie Mellon University, Pittsburgh, PA, USA"}],"role":[{"role":"author","vocabulary":"crossref"}]},{"given":"Andrew","family":"Pavlo","sequence":"additional","affiliation":[{"name":"Carnegie Mellon University, Pittsburgh, PA, USA"}],"role":[{"role":"author","vocabulary":"crossref"}]}],"member":"320","published-online":{"date-parts":[[2026,4,23]]},"reference":[{"key":"e_1_2_1_1_1","volume-title":"Techniques & Tools. pearson Education","author":"Alfred V Aho","year":"2007","unstructured":"V Aho Alfred, S Lam Monica, and D Ullman Jeffrey. Compilers Principles, Techniques & Tools. pearson Education, 2007."},{"key":"e_1_2_1_2_1","doi-asserted-by":"publisher","DOI":"10.1145\/390013.808479"},{"key":"e_1_2_1_3_1","doi-asserted-by":"publisher","DOI":"10.1145\/2499370.2462180"},{"key":"e_1_2_1_4_1","doi-asserted-by":"publisher","DOI":"10.1145\/75277.75280"},{"key":"e_1_2_1_5_1","volume-title":"When Does Scalar UDF Inlining Work In SQL Server? https:\/\/erikdarlingdata.com\/ when-does-udf-inlining-kick-in\/, may","author":"Darling E.","year":"2022","unstructured":"E. Darling. When Does Scalar UDF Inlining Work In SQL Server? https:\/\/erikdarlingdata.com\/ when-does-udf-inlining-kick-in\/, may 2022."},{"key":"e_1_2_1_6_1","volume-title":"Group by never completes #9718. https:\/\/github.com\/duckdb\/duckdb\/issues\/9718, november","author":"DB.","year":"2023","unstructured":"DuckDB. Group by never completes #9718. https:\/\/github.com\/duckdb\/duckdb\/issues\/9718, november 2023."},{"key":"e_1_2_1_7_1","volume-title":"Overview of DuckDB Internals. https:\/\/duckdb.org\/docs\/internals\/overview.html, april","author":"DB.","year":"2024","unstructured":"DuckDB. Overview of DuckDB Internals. https:\/\/duckdb.org\/docs\/internals\/overview.html, april 2024."},{"key":"e_1_2_1_8_1","first-page":"993","volume-title":"Proceedings of the 2007 ACM SIGMOD Conference","author":"M","year":"2007","unstructured":"M Elhemali et al. Execution strategies for sql subqueries. In Proceedings of the 2007 ACM SIGMOD Conference, pages 993-1004, 2007."},{"key":"e_1_2_1_9_1","first-page":"1663","volume-title":"Proceedings of the 2017 ACM Conference","author":"Emani K Venkatesh","year":"2017","unstructured":"K Venkatesh Emani, Tejas Deshpande, Karthik Ramachandra, and S Sudarshan. Dbridge: Translating imperative code to sql. In Proceedings of the 2017 ACM Conference, pages 1663-1666, 2017."},{"key":"e_1_2_1_10_1","doi-asserted-by":"publisher","DOI":"10.1145\/2882903.2882926"},{"key":"e_1_2_1_11_1","volume-title":"Pyfroid: Scaling data analysis on a commodity workstation","author":"Emani Venkatesh","year":"2024","unstructured":"Venkatesh Emani, Avrilia Floratou, and Carlo Curino. Pyfroid: Scaling data analysis on a commodity workstation. 2024."},{"key":"e_1_2_1_12_1","volume-title":"Speed up execution of your functions inside SQL statements with UDF pragma . https:\/\/stevenfeuersteinonplsql.blogspot.com\/2017\/ 03\/speed-up-execution-of-your-functions.html, march","author":"Feuerstein Steven","year":"2017","unstructured":"Steven Feuerstein. Speed up execution of your functions inside SQL statements with UDF pragma . https:\/\/stevenfeuersteinonplsql.blogspot.com\/2017\/ 03\/speed-up-execution-of-your-functions.html, march 2017."},{"key":"e_1_2_1_13_1","doi-asserted-by":"publisher","DOI":"10.14778\/3554821.3554886"},{"key":"e_1_2_1_14_1","volume-title":"Proc. VLDB Endow., 15(10)","author":"Foufoulas Yannis","year":"2022","unstructured":"Yannis Foufoulas, Alkis Simitsis, Lefteris Stamatogiannakis, and Yannis Ioannidis. YeSQL: \u201dyou extend SQL\u201d with rich and highly performant user-defined functions in relational databases. Proc. VLDB Endow., 15(10), June 2022."},{"key":"e_1_2_1_15_1","volume-title":"CIDR 2024, Conference on Innovative Data Systems Research","author":"Franz Kai","year":"2024","unstructured":"Kai Franz, Samuel I Arch, Denis Hirn, Torsten Grust, Todd Mowry, and Pavlo. Dear user-defined functions, inlining isn\u2019t working out so great for us. let\u2019s try batching to make our relationship work. sincerely, sql. In CIDR 2024, Conference on Innovative Data Systems Research, 2024."},{"key":"e_1_2_1_16_1","doi-asserted-by":"publisher","DOI":"10.1145\/376284.375748"},{"key":"e_1_2_1_17_1","doi-asserted-by":"publisher","DOI":"10.14778\/3489496.3489501"},{"key":"e_1_2_1_18_1","doi-asserted-by":"publisher","DOI":"10.1145\/3318464.3389736"},{"key":"e_1_2_1_19_1","doi-asserted-by":"publisher","DOI":"10.14778\/3457390.3457402"},{"key":"e_1_2_1_20_1","doi-asserted-by":"publisher","DOI":"10.14778\/1453856.1453975"},{"key":"e_1_2_1_21_1","doi-asserted-by":"publisher","DOI":"10.1145\/800152.804919"},{"key":"e_1_2_1_22_1","doi-asserted-by":"publisher","DOI":"10.1145\/3448016.3457272"},{"key":"e_1_2_1_23_1","volume-title":"Decorrelation and parallelization of recursive and materialized CTEs #10357. https:\/\/github.com\/duckdb\/duckdb\/pull\/10357, feb","author":"Hirn Denis","year":"2023","unstructured":"Denis Hirn. Decorrelation and parallelization of recursive and materialized CTEs #10357. https:\/\/github.com\/duckdb\/duckdb\/pull\/10357, feb 2023."},{"key":"e_1_2_1_24_1","volume-title":"https:\/\/apfel-db.cs.uni-tuebingen.de\/, may","author":"Hirn Denis","year":"2024","unstructured":"Denis Hirn. Apfel. https:\/\/apfel-db.cs.uni-tuebingen.de\/, may 2024."},{"key":"e_1_2_1_25_1","volume-title":"Magpie: Python at Speed and Scale using Cloud Backends","author":"A Jindal","year":"2021","unstructured":"A Jindal et al. Magpie: Python at Speed and Scale using Cloud Backends. 2021."},{"key":"e_1_2_1_26_1","doi-asserted-by":"publisher","DOI":"10.1145\/178243.178258"},{"key":"e_1_2_1_27_1","doi-asserted-by":"publisher","DOI":"10.14778\/3611479.3611539"},{"key":"e_1_2_1_28_1","doi-asserted-by":"publisher","DOI":"10.14778\/3551793.3551801"},{"key":"e_1_2_1_29_1","volume-title":"On optimizing an sql-like nested query. ACM Transactions on Database Systems (TODS), 7(3):443-469","author":"Kim Won","year":"1982","unstructured":"Won Kim. On optimizing an sql-like nested query. ACM Transactions on Database Systems (TODS), 7(3):443-469, 1982."},{"key":"e_1_2_1_30_1","volume-title":"CIDR Conference","author":"Kl\u00a8abe Steffen","year":"2022","unstructured":"Steffen Kl\u00a8abe, Bobby DeSantis, Stefan Hagedorn, and Kai-Uwe Sattler. Accelerating python udfs in vectorized query execution. CIDR Conference, 2022."},{"issue":"1","key":"e_1_2_1_31_1","first-page":"132","article-title":"Design of the java hotspot\u2122 client compiler for java 6","volume":"5","author":"T Kotzmann","year":"2008","unstructured":"T Kotzmann et al. Design of the java hotspot\u2122 client compiler for java 6. ACM TACO, 5(1):132, 2008.","journal-title":"ACM TACO"},{"key":"e_1_2_1_32_1","first-page":"1177","volume-title":"Proceedings of the 2011 ACM SIGMOD Conference","year":"2011","unstructured":"P-?A Larson et al. Sql server column store indexes. In Proceedings of the 2011 ACM SIGMOD Conference, pages 1177-1184, 2011."},{"key":"e_1_2_1_33_1","first-page":"2","volume-title":"2021 IEEE\/ACM CGO","author":"C","year":"2021","unstructured":"C Lattner et al. Mlir: Scaling compiler infrastructure for domain specific computation. In 2021 IEEE\/ACM CGO, pages 2-14, 2021."},{"key":"e_1_2_1_34_1","doi-asserted-by":"publisher","DOI":"10.1109\/CGO.2004.1281665"},{"key":"e_1_2_1_35_1","doi-asserted-by":"publisher","DOI":"10.1145\/2588555.2610507"},{"key":"e_1_2_1_36_1","volume-title":"Polysem: Efficient polyglot analytics on semantic data","author":"Liu X","year":"2023","unstructured":"X Liu, V Emani, A Floratou, J Cahoon, P Seamark, and C Curino. Polysem: Efficient polyglot analytics on semantic data. 2023."},{"key":"e_1_2_1_37_1","volume-title":"Scalar User-Defined Functions for In-Memory OLTP. https:\/\/learn.microsoft.com\/ en-us\/sql\/relational-databases\/in-memory-oltp\/ scalar-user-defined-functions-for-in-memory-oltp\/, june","year":"2016","unstructured":"Microsoft. Scalar User-Defined Functions for In-Memory OLTP. https:\/\/learn.microsoft.com\/ en-us\/sql\/relational-databases\/in-memory-oltp\/ scalar-user-defined-functions-for-in-memory-oltp\/, june 2016."},{"key":"e_1_2_1_38_1","volume-title":"Technologie und Web (BTW 2015)","author":"Neumann Thomas","year":"2015","unstructured":"Thomas Neumann and Alfons Kemper. Unnesting arbitrary queries. Datenbanksysteme f\u00a8ur Business, Technologie und Web (BTW 2015), 2015."},{"key":"e_1_2_1_39_1","volume-title":"Oracle9i Database New Features. https:\/\/docs.oracle.com\/cd\/A91202_01\/901_doc\/ server.901\/a90120\/ch2_feat.htm, june","year":"2001","unstructured":"Oracle. Oracle9i Database New Features. https:\/\/docs.oracle.com\/cd\/A91202_01\/901_doc\/ server.901\/a90120\/ch2_feat.htm, june 2001."},{"key":"e_1_2_1_40_1","volume-title":"Correlated Subqueries in SQL. https:\/\/duckdb.org\/2023\/05\/26\/ correlated-subqueries-in-sql.html, may","author":"Raasveldt Mark","year":"2023","unstructured":"Mark Raasveldt. Correlated Subqueries in SQL. https:\/\/duckdb.org\/2023\/05\/26\/ correlated-subqueries-in-sql.html, may 2023."},{"key":"e_1_2_1_41_1","first-page":"1981","volume-title":"Proceedings of the 2019 SIGMOD Conference","author":"Raasveldt Mark","year":"2019","unstructured":"Mark Raasveldt and Hannes M\u00a8uhleisen. Duckdb: an embeddable analytical database. In Proceedings of the 2019 SIGMOD Conference, pages 1981-1984, 2019."},{"key":"e_1_2_1_42_1","doi-asserted-by":"publisher","DOI":"10.1145\/3186728.3164140"},{"key":"e_1_2_1_43_1","doi-asserted-by":"publisher","DOI":"10.1145\/73560.73562"},{"key":"e_1_2_1_44_1","doi-asserted-by":"publisher","DOI":"10.1109\/ICDE.1996.492194"},{"key":"e_1_2_1_45_1","doi-asserted-by":"publisher","DOI":"10.1109\/ICDE.2014.6816679"},{"key":"e_1_2_1_46_1","volume-title":"SingleStoreDB Cloud Release Notes. https:\/\/docs.singlestore.com\/cloud\/release-notes\/ singlestoredb-cloud-release-notes\/, november","year":"2021","unstructured":"SingleStore. SingleStoreDB Cloud Release Notes. https:\/\/docs.singlestore.com\/cloud\/release-notes\/ singlestoredb-cloud-release-notes\/, november 2021."},{"key":"e_1_2_1_47_1","first-page":"1718","volume-title":"Proceedings of the 2021 SIGMOD Conference","author":"L","year":"2021","unstructured":"L Spiegelberg et al. Tuplex: Data Science in Python at Native Code Speed. In Proceedings of the 2021 SIGMOD Conference, pages 1718-1731. ACM, June 2021."},{"key":"e_1_2_1_48_1","doi-asserted-by":"publisher","DOI":"10.14778\/3352063.3352109"},{"key":"e_1_2_1_49_1","first-page":"187","volume-title":"Proceedings of the 2013 ACM international symposium on New ideas, new paradigms, and reflections on programming & software","author":"T","year":"2013","unstructured":"T W\u00a8urthinger et al. One vm to rule them all. In Proceedings of the 2013 ACM international symposium on New ideas, new paradigms, and reflections on programming & software, pages 187-204, 2013."},{"key":"e_1_2_1_50_1","doi-asserted-by":"publisher","DOI":"10.1145\/3485489"}],"container-title":["ACM SIGMOD Record"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.1145\/3810900.3810914","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2026,4,23]],"date-time":"2026-04-23T18:16:42Z","timestamp":1776968202000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.1145\/3810900.3810914"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2026,4,23]]},"references-count":50,"journal-issue":{"issue":"1","published-print":{"date-parts":[[2026,4,23]]}},"alternative-id":["10.1145\/3810900.3810914"],"URL":"https:\/\/doi.org\/10.1145\/3810900.3810914","relation":{},"ISSN":["0163-5808"],"issn-type":[{"value":"0163-5808","type":"print"}],"subject":[],"published":{"date-parts":[[2026,4,23]]},"assertion":[{"value":"2026-04-23","order":3,"name":"published","label":"Published","group":{"name":"publication_history","label":"Publication History"}}]}}