research-article

Efficient Execution of User-Defined Functions in SQL Queries

Authors:

Yannis Foufoulas,

Alkis SimitsisAuthors Info & Claims

Proceedings of the VLDB Endowment, Volume 16, Issue 12

Pages 3874 - 3877

https://doi.org/10.14778/3611540.3611574

Published: 01 August 2023 Publication History

Abstract

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.

References

[1]

Mark Blacher, Joachim Giesen, Sören Laue, Julien Klaus, and Viktor Leis. 2022. Machine Learning, Linear Algebra, and More: Is SQL All You Need?. In CIDR.

[2]

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).

[3]

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.

[4]

Alvin Cheung, Armando Solar-Lezama, and Samuel Madden. 2013. Optimizing database-backed applications with query synthesis. In SIGPLAN. 3--14.

[5]

Andrew Crotty, Alex Galakatos, Kayhan Dursun, Tim Kraska, Carsten Binnig, Ugur Çetintemel, and Stan Zdonik. 2015. An Architecture for Compiling UDF-centric Workflows. PVLDB 8, 12 (2015), 1466--1477.

Digital Library

[6]

Joseph Vinish D'silva, Florestan De Moor, and Bettina Kemme. 2018. AIDA - Abstraction for Advanced In-Database Analytics. PVLDB 11, 11 (2018).

[7]

Christian Duta and Torsten Grust. 2020. Functional-Style SQL UDFs With a Capital 'F'. In SIGMOD. 1273--1287.

[8]

Christian Duta, Denis Hirn, and Torsten Grust. 2020. Compiling PL/SQL Away. In CIDR.

[9]

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.

[10]

Grégory 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.

[11]

Tim Fischer, Denis Hirn, and Torsten Grust. 2022. Snakes on a Plan: Compiling Python Functions into Plain SQL Queries. In SIGMOD. ACM, 2389--2392.

[12]

Yannis E. Foufoulas and Alkis Simitsis. 2023. User-Defined Functions in Modern Data Engines. In ICDE. IEEE.

[13]

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).

[14]

Philipp Marian Grulich, Steffen Zeuch, and Volker Markl. 2022. Babelfish: Efficient Execution of Polyglot Queries. PVLDB 15, 2 (2022), 196--210.

[15]

Surabhi Gupta, Sanket Purandare, and Karthik Ramachandra. 2020. Aggify: Lifting the Curse of Cursor Loops using Custom Aggregates. In SIGMOD.

[16]

Immanuel Haffner and Jens Dittrich. 2023. A simplified Architecture for Fast, Adaptive Compilation and Execution of SQL Queries. In EDBT. 1--13.

[17]

Stefan Hagedorn, Steffen Kläbe, and Kai-Uwe Sattler. 2021. Putting Pandas in a Box. In CIDR.

[18]

Anna Herlihy, Periklis Chrysogelos, and Anastasia Ailamaki. 2022. Boosting Efficiency of External Pipelines by Blurring ApplicationBoundaries. In CIDR.

[19]

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.

[20]

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.

[21]

Petar Jovanovic, Alkis Simitsis, and Kevin Wilkinson. 2014. BabbleFlow: a translator for analytic data flow programs. In SIGMOD. 713--716.

[22]

Petar Jovanovic, Alkis Simitsis, and Kevin Wilkinson. 2014. Engine independence for logical analytic flows. In ICDE. 1060--1071.

[23]

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.

[24]

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).

[25]

Steffen Kläbe, Robert DeSantis, Stefan Hagedorn, and Kai-Uwe Sattler. 2022. Accelerating Python UDFs in Vectorized Query Execution. In CIDR.

[26]

Chris Lattner and Vikram S. Adve. 2004. LLVM: A Compilation Framework for Lifelong Program Analysis & Transformation. In IEEE/ACM CGO. 75--88.

Digital Library

[27]

MonetDB. 2022. User Defined Functions. Available at: https://www.monetdb.org/documentation-Sep2022/dev-guide/sql-extensions/user-defined-functions.

[28]

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.

[29]

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.

[30]

PostgreSQL. 2022. PL/pgSQL, SQL Procedural Language. Available at: https://www.postgresql.org/docs/current/plpgsql.html.

[31]

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

[32]

PySpark. 2022. Available at: https://pypi.org/project/pyspark.

[33]

Mark Raasveldt and Hannes Mühleisen. 2016. Vectorized UDFs in Column-Stores. In SSDBM. 16:1--16:12.

[34]

Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, César A. Galindo-Legaria, and Conor Cunningham. 2017. Froid: Optimization of Imperative Programs in a Relational Database. PVLDB 11, 4 (2017), 432--444.

[35]

Astrid Rheinländer, Ulf Leser, and Goetz Graefe. 2017. Optimization of Complex Dataflows with User-Defined Functions. ACM Comput. Surv. 50, 3 (2017).

[36]

Viktor Rosenfeld, René Müller, Pinar Tözün, and Fatma Özcan. 2017. Processing Java UDFs in a C++ environment. In SoCC. 419--431.

[37]

Maximilian E. Schüle, 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.

[38]

Maximilian E. Schüle, 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.

[39]

Moritz Sichert and Thomas Neumann. 2022. User-Defined Operators: Efficiently Integrating Custom Algorithms into Modern Databases. PVLDB 15, 5 (2022).

Digital Library

[40]

Varun Simhadri, Karthik Ramachandra, Arun Chaitanya, Ravindra Guravannavar, and S. Sudarshan. 2014. Decorrelation of user defined function invocations in queries. In ICDE. 532--543.

[41]

Alkis Simitsis and Kevin Wilkinson. 2014. The specification for xLM: an encoding for analytic flows. Technical Report, HP Labs.

[42]

Leonhard F Spiegelberg, Rahul Yesantharao, Malt Schwarzkopf, and Tim Kraska. 2021. Tuplex: Data Science in Python at Native Code Speed. In SIGMOD.

Digital Library

[43]

Guoqiang Zhang, Yuanchao Xu, Xipeng Shen, and Isil Dillig. 2021. UDF to SQL translation through compositional lazy inductive synthesis. OOPSLA 5 (2021).

Cited By

Zhang CFarouk T(2024)Sharing Queries with Nonequivalent User-defined Aggregate FunctionsACM Transactions on Database Systems10.1145/364913349:2(1-46)Online publication date: 10-Apr-2024
https://dl.acm.org/doi/10.1145/3649133
Chasialis KPalaiologou TFoufoulas YSimitsis AIoannidis Y(2024)QFusor: A UDF Optimizer Plugin for SQL Databases2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00427(5457-5460)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00427
Kontaxakis ASacharidis DSimitsis AAbelló ANadal S(2024)HYPPO: Using Equivalences to Optimize Pipelines in Exploratory Machine Learning2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00024(221-234)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00024
Show More Cited By

Recommendations

Comments

Information & Contributors

Information

Published In

cover image Proceedings of the VLDB Endowment

Proceedings of the VLDB Endowment Volume 16, Issue 12

August 2023

685 pages

ISSN:2150-8097

Editors:
Georgia Koutrika
Athena Research Center
,
Jun Yang
Duke University

Issue’s Table of Contents

Publisher

VLDB Endowment

Publication History

Published: 01 August 2023

Published in PVLDB Volume 16, Issue 12

Check for updates

Qualifiers

Research-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

4
Total Citations
View Citations
261
Total Downloads

Downloads (Last 12 months)179
Downloads (Last 6 weeks)26

Reflects downloads up to 25 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

Zhang CFarouk T(2024)Sharing Queries with Nonequivalent User-defined Aggregate FunctionsACM Transactions on Database Systems10.1145/364913349:2(1-46)Online publication date: 10-Apr-2024
https://dl.acm.org/doi/10.1145/3649133
Chasialis KPalaiologou TFoufoulas YSimitsis AIoannidis Y(2024)QFusor: A UDF Optimizer Plugin for SQL Databases2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00427(5457-5460)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00427
Kontaxakis ASacharidis DSimitsis AAbelló ANadal S(2024)HYPPO: Using Equivalences to Optimize Pipelines in Exploratory Machine Learning2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00024(221-234)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00024
Karvelas AFoufoulas YSimitsis AIoannidis Y(2024)Investigating Learning Join Order Optimization Strategies for Rule-based Data EnginesInformation Systems Frontiers10.1007/s10796-024-10555-1Online publication date: 22-Nov-2024
https://doi.org/10.1007/s10796-024-10555-1

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents