{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2026,9,4]],"date-time":"2026-09-04T00:42:37Z","timestamp":1788482557094,"version":"build-2803163510"},"reference-count":43,"publisher":"Association for Computing Machinery (ACM)","issue":"12","content-domain":{"domain":["dl.acm.org"],"crossmark-restriction":true},"short-container-title":["Proc. VLDB Endow."],"published-print":{"date-parts":[[2023,8]]},"abstract":"<jats:p>User-defined functions (UDFs) have been widely used to overcome the expressivity limitations of SQL and complement its declarative nature with functional capabilities. UDFs are particularly useful in today's applications that involve complex data analytics and machine learning algorithms and logic. However, UDFs pose significant performance challenges in query processing and optimization, largely due to the mismatch of the UDF execution and SQL processing environments. In this tutorial, we present state-of-the-art methods and systems towards efficient execution of UDFs in SQL queries. We focus on low-level techniques for physical optimization and compilation of UDF queries, describe and compare the core, recent approaches in the area, discuss their advantages and limitations, identify critical gaps in theory and practice, and propose promising future research directions.<\/jats:p>","DOI":"10.14778\/3611540.3611574","type":"journal-article","created":{"date-parts":[[2023,9,15]],"date-time":"2023-09-15T11:32:37Z","timestamp":1694777557000},"page":"3874-3877","update-policy":"https:\/\/doi.org\/10.1145\/crossmark-policy","source":"Crossref","is-referenced-by-count":17,"title":["Efficient Execution of User-Defined Functions in SQL Queries"],"prefix":"10.14778","volume":"16","author":[{"given":"Yannis","family":"Foufoulas","sequence":"first","affiliation":[{"name":"University of Athens, Athena R.C., Athens, Greece"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Alkis","family":"Simitsis","sequence":"additional","affiliation":[{"name":"Athena Research Center, Athens, Greece"}],"role":[{"vocabulary":"crossref","role":"author"}]}],"member":"320","published-online":{"date-parts":[[2023,8]]},"reference":[{"key":"e_1_2_1_1_1","unstructured":"Mark Blacher Joachim Giesen S\u00f6ren Laue Julien Klaus and Viktor Leis. 2022. Machine Learning Linear Algebra and More: Is SQL All You Need?. In CIDR."},{"key":"e_1_2_1_2_1","volume-title":"On Optimizing Operator Fusion Plans for Large-Scale Machine Learning in SystemML. PVLDB 11, 12","author":"Boehm Matthias","year":"2018","unstructured":"Matthias Boehm, Berthold Reinwald, Dylan Hutchison, Prithviraj Sen, Alexandre V. Evfimievski, and Niketan Pansare. 2018. On Optimizing Operator Fusion Plans for Large-Scale Machine Learning in SystemML. PVLDB 11, 12 (2018)."},{"key":"e_1_2_1_3_1","volume-title":"Laurie J. Hendren, and Bettina Kemme.","author":"Chen Hanfeng","year":"2021","unstructured":"Hanfeng Chen, Joseph Vinish D'silva, Laurie J. Hendren, and Bettina Kemme. 2021. HorsePower: Accelerating Database Queries for Advanced Data Analytics. In EDBT. 361--366."},{"key":"e_1_2_1_4_1","doi-asserted-by":"crossref","unstructured":"Alvin Cheung Armando Solar-Lezama and Samuel Madden. 2013. Optimizing database-backed applications with query synthesis. In SIGPLAN. 3--14.","DOI":"10.1145\/2491956.2462180"},{"key":"e_1_2_1_5_1","doi-asserted-by":"publisher","DOI":"10.14778\/2824032.2824045"},{"key":"e_1_2_1_6_1","volume-title":"Florestan De Moor, and Bettina Kemme","author":"D'silva Joseph Vinish","year":"2018","unstructured":"Joseph Vinish D'silva, Florestan De Moor, and Bettina Kemme. 2018. AIDA - Abstraction for Advanced In-Database Analytics. PVLDB 11, 11 (2018)."},{"key":"e_1_2_1_7_1","doi-asserted-by":"crossref","unstructured":"Christian Duta and Torsten Grust. 2020. Functional-Style SQL UDFs With a Capital 'F'. In SIGMOD. 1273--1287.","DOI":"10.1145\/3318464.3389707"},{"key":"e_1_2_1_8_1","unstructured":"Christian Duta Denis Hirn and Torsten Grust. 2020. Compiling PL\/SQL Away. In CIDR."},{"key":"e_1_2_1_9_1","doi-asserted-by":"crossref","unstructured":"K. Venkatesh Emani Karthik Ramachandra Subhro Bhattacharya and S. Sudarshan. 2016. Extracting Equivalent SQL from Imperative Code in Database Applications. In SIGMOD. ACM 1781--1796.","DOI":"10.1145\/2882903.2882926"},{"key":"e_1_2_1_10_1","volume-title":"Flare: Optimizing Apache Spark with Native Compilation for Scale-Up Architectures and Medium-Size Data. In USENIX.","author":"Essertel Gr\u00e9gory M.","year":"2018","unstructured":"Gr\u00e9gory M. Essertel, Ruby Y. Tahboub, James M. Decker, Kevin J. Brown, Kunle Olukotun, and Tiark Rompf. 2018. Flare: Optimizing Apache Spark with Native Compilation for Scale-Up Architectures and Medium-Size Data. In USENIX."},{"key":"e_1_2_1_11_1","doi-asserted-by":"crossref","unstructured":"Tim Fischer Denis Hirn and Torsten Grust. 2022. Snakes on a Plan: Compiling Python Functions into Plain SQL Queries. In SIGMOD. ACM 2389--2392.","DOI":"10.1145\/3514221.3520175"},{"key":"e_1_2_1_12_1","volume-title":"Foufoulas and Alkis Simitsis","author":"Yannis","year":"2023","unstructured":"Yannis E. Foufoulas and Alkis Simitsis. 2023. User-Defined Functions in Modern Data Engines. In ICDE. IEEE."},{"key":"e_1_2_1_13_1","volume-title":"Ioannidis","author":"Foufoulas Yannis E.","year":"2022","unstructured":"Yannis E. Foufoulas, Alkis Simitsis, Eleftherios Stamatogiannakis, and Yannis E. Ioannidis. 2022. YeSQL: \"You extend SQL\" with Rich and Highly Performant User-Defined Functions in Relational Databases. PVLDB 15, 10 (2022)."},{"key":"e_1_2_1_14_1","first-page":"196","article-title":"Babelfish: Efficient Execution of Polyglot Queries","volume":"15","author":"Grulich Philipp Marian","year":"2022","unstructured":"Philipp Marian Grulich, Steffen Zeuch, and Volker Markl. 2022. Babelfish: Efficient Execution of Polyglot Queries. PVLDB 15, 2 (2022), 196--210.","journal-title":"PVLDB"},{"key":"e_1_2_1_15_1","volume-title":"Aggify: Lifting the Curse of Cursor Loops using Custom Aggregates. In SIGMOD.","author":"Gupta Surabhi","year":"2020","unstructured":"Surabhi Gupta, Sanket Purandare, and Karthik Ramachandra. 2020. Aggify: Lifting the Curse of Cursor Loops using Custom Aggregates. In SIGMOD."},{"key":"e_1_2_1_16_1","unstructured":"Immanuel Haffner and Jens Dittrich. 2023. A simplified Architecture for Fast Adaptive Compilation and Execution of SQL Queries. In EDBT. 1--13."},{"key":"e_1_2_1_17_1","unstructured":"Stefan Hagedorn Steffen Kl\u00e4be and Kai-Uwe Sattler. 2021. Putting Pandas in a Box. In CIDR."},{"key":"e_1_2_1_18_1","unstructured":"Anna Herlihy Periklis Chrysogelos and Anastasia Ailamaki. 2022. Boosting Efficiency of External Pipelines by Blurring ApplicationBoundaries. In CIDR."},{"key":"e_1_2_1_19_1","doi-asserted-by":"crossref","unstructured":"Fabian Hueske Mathias Peters Aljoscha Krettek Matthias Ringwald Kostas Tzoumas Volker Markl and Johann-Christoph Freytag. 2013. Peeking into the optimization of data flow programs with MapReduce-style UDFs. In ICDE.","DOI":"10.1109\/ICDE.2013.6544927"},{"key":"e_1_2_1_20_1","volume-title":"Andreas Mueller, Wentao Wu, and Hiren Patel.","author":"Jindal Alekh","year":"2021","unstructured":"Alekh Jindal, K. Venkatesh Emani, Maureen Daum, Olga Poppe, Brandon Haynes, Anna Pavlenko, Ayushi Gupta, Karthik Ramachandra, Carlo Curino, Andreas Mueller, Wentao Wu, and Hiren Patel. 2021. Magpie: Python at Speed and Scale using Cloud Backends. In CIDR."},{"key":"e_1_2_1_21_1","doi-asserted-by":"crossref","unstructured":"Petar Jovanovic Alkis Simitsis and Kevin Wilkinson. 2014. BabbleFlow: a translator for analytic data flow programs. In SIGMOD. 713--716.","DOI":"10.1145\/2588555.2594534"},{"key":"e_1_2_1_22_1","doi-asserted-by":"crossref","unstructured":"Petar Jovanovic Alkis Simitsis and Kevin Wilkinson. 2014. Engine independence for logical analytic flows. In ICDE. 1060--1071.","DOI":"10.1109\/ICDE.2014.6816723"},{"key":"e_1_2_1_23_1","unstructured":"Konstantinos Karanasos Matteo Interlandi Fotis Psallidas Rathijit Sen Kwanghyun Park Ivan Popivanov Doris Xin Supun Nakandala Subru Krishnan Markus Weimer Yuan Yu Raghu Ramakrishnan and Carlo Curino. 2020. Extending Relational Query Processing with ML Inference. In CIDR."},{"key":"e_1_2_1_24_1","volume-title":"Boncz","author":"Kersten Timo","year":"2018","unstructured":"Timo Kersten, Viktor Leis, Alfons Kemper, Thomas Neumann, Andrew Pavlo, and Peter A. Boncz. 2018. Everything You Always Wanted to Know About Compiled and Vectorized Queries But Were Afraid to Ask. PVLDB 11, 13 (2018)."},{"key":"e_1_2_1_25_1","unstructured":"Steffen Kl\u00e4be Robert DeSantis Stefan Hagedorn and Kai-Uwe Sattler. 2022. Accelerating Python UDFs in Vectorized Query Execution. In CIDR."},{"key":"e_1_2_1_26_1","volume-title":"Adve","author":"Lattner Chris","year":"2004","unstructured":"Chris Lattner and Vikram S. Adve. 2004. LLVM: A Compilation Framework for Lifelong Program Analysis & Transformation. In IEEE\/ACM CGO. 75--88."},{"key":"e_1_2_1_27_1","unstructured":"MonetDB. 2022. User Defined Functions. Available at: https:\/\/www.monetdb.org\/documentation-Sep2022\/dev-guide\/sql-extensions\/user-defined-functions."},{"key":"e_1_2_1_28_1","doi-asserted-by":"crossref","unstructured":"Shoumik Palkar James Thomas Deepak Narayanan Pratiksha Thaker Rahul Palamuttam Parimarjan Negi Anil Shanbhag Malte Schwarzkopf Holger Pirk Saman P. Amarasinghe Samuel Madden and Matei Zaharia. 2018. Evaluating End-to-End Optimization for Data Analytics Applications in Weld. In PVLDB.","DOI":"10.14778\/3213880.3213890"},{"key":"e_1_2_1_29_1","unstructured":"Shoumik Palkar James Thomas Anil Shanbhag Malte Schwarzkopf Saman P. Amarasinghe and Matei Zaharia. 2017. A Common Runtime for High Performance Data Analysis. In CIDR."},{"key":"e_1_2_1_30_1","unstructured":"PostgreSQL. 2022. PL\/pgSQL SQL Procedural Language. Available at: https:\/\/www.postgresql.org\/docs\/current\/plpgsql.html."},{"key":"e_1_2_1_31_1","volume-title":"Carlo Curino, and Markus Weimer","author":"Psallidas Fotis","year":"2019","unstructured":"Fotis Psallidas, Yiwen Zhu, Bojan Karlas, Matteo Interlandi, Avrilia Floratou, Konstantinos Karanasos, Wentao Wu, Ce Zhang, Subru Krishnan, Carlo Curino, and Markus Weimer. 2019. Data Science through the looking glass and what we found there. CoRR abs\/1912.09536 (2019). http:\/\/arxiv.org\/abs\/1912.09536"},{"key":"e_1_2_1_32_1","unstructured":"PySpark. 2022. Available at: https:\/\/pypi.org\/project\/pyspark."},{"key":"e_1_2_1_33_1","first-page":"1","article-title":"Vectorized UDFs in Column-Stores","volume":"16","author":"Raasveldt Mark","year":"2016","unstructured":"Mark Raasveldt and Hannes M\u00fchleisen. 2016. Vectorized UDFs in Column-Stores. In SSDBM. 16:1--16:12.","journal-title":"SSDBM."},{"key":"e_1_2_1_34_1","first-page":"432","article-title":"Froid: Optimization of Imperative Programs in a Relational Database","volume":"11","author":"Ramachandra Karthik","year":"2017","unstructured":"Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, C\u00e9sar A. Galindo-Legaria, and Conor Cunningham. 2017. Froid: Optimization of Imperative Programs in a Relational Database. PVLDB 11, 4 (2017), 432--444.","journal-title":"PVLDB"},{"key":"e_1_2_1_35_1","volume-title":"Optimization of Complex Dataflows with User-Defined Functions. ACM Comput. Surv. 50, 3","author":"Rheinl\u00e4nder Astrid","year":"2017","unstructured":"Astrid Rheinl\u00e4nder, Ulf Leser, and Goetz Graefe. 2017. Optimization of Complex Dataflows with User-Defined Functions. ACM Comput. Surv. 50, 3 (2017)."},{"key":"e_1_2_1_36_1","doi-asserted-by":"crossref","unstructured":"Viktor Rosenfeld Ren\u00e9 M\u00fcller Pinar T\u00f6z\u00fcn and Fatma \u00d6zcan. 2017. Processing Java UDFs in a C++ environment. In SoCC. 419--431.","DOI":"10.1145\/3127479.3132022"},{"key":"e_1_2_1_37_1","first-page":"1","article-title":"Freedom for the SQL-Lambda","volume":"6","author":"Sch\u00fcle Maximilian E.","year":"2020","unstructured":"Maximilian E. Sch\u00fcle, Jakob Huber, Alfons Kemper, and Thomas Neumann. 2020. Freedom for the SQL-Lambda: Just-in-Time-Compiling User-Injected Functions in PostgreSQL. In SSDBM. 6:1--6:12.","journal-title":"Just-in-Time-Compiling User-Injected Functions in PostgreSQL. In SSDBM."},{"key":"e_1_2_1_38_1","unstructured":"Maximilian E. Sch\u00fcle Luca Scalerandi Alfons Kemper and Thomas Neumann. 2023. Blue Elephants Inspecting Pandas: Inspection and Execution of Machine Learning Pipelines in SQL. In EDBT. OpenProceedings.org 40--52."},{"key":"e_1_2_1_39_1","doi-asserted-by":"publisher","DOI":"10.14778\/3510397.3510408"},{"key":"e_1_2_1_40_1","doi-asserted-by":"crossref","unstructured":"Varun Simhadri Karthik Ramachandra Arun Chaitanya Ravindra Guravannavar and S. Sudarshan. 2014. Decorrelation of user defined function invocations in queries. In ICDE. 532--543.","DOI":"10.1109\/ICDE.2014.6816679"},{"key":"e_1_2_1_41_1","unstructured":"Alkis Simitsis and Kevin Wilkinson. 2014. The specification for xLM: an encoding for analytic flows. Technical Report HP Labs."},{"key":"e_1_2_1_42_1","doi-asserted-by":"publisher","DOI":"10.1145\/3448016.3457244"},{"key":"e_1_2_1_43_1","volume-title":"UDF to SQL translation through compositional lazy inductive synthesis. OOPSLA 5","author":"Zhang Guoqiang","year":"2021","unstructured":"Guoqiang Zhang, Yuanchao Xu, Xipeng Shen, and Isil Dillig. 2021. UDF to SQL translation through compositional lazy inductive synthesis. OOPSLA 5 (2021)."}],"container-title":["Proceedings of the VLDB Endowment"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.14778\/3611540.3611574","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2025,9,10]],"date-time":"2025-09-10T22:35:42Z","timestamp":1757543742000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.14778\/3611540.3611574"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2023,8]]},"references-count":43,"journal-issue":{"issue":"12","published-print":{"date-parts":[[2023,8]]}},"alternative-id":["10.14778\/3611540.3611574"],"URL":"https:\/\/doi.org\/10.14778\/3611540.3611574","relation":{},"ISSN":["2150-8097"],"issn-type":[{"value":"2150-8097","type":"print"}],"subject":[],"published":{"date-parts":[[2023,8]]},"assertion":[{"value":"2023-08-01","order":3,"name":"published","label":"Published","group":{"name":"publication_history","label":"Publication History"}}]}}