User-defined functions (UDFs) extend the expressiveness of declarative SQL with functional capabilities, but also pose a core bottleneck in query processing due to the impedance mismatch between the UDF and SQL execution environments, and the limitations of the query optimizers to consistently produce good plans for UDF queries. Research and commercial approaches propose remedies for performant UDF query execution ranging from logical optimization and heuristics to physical optimization and compilation techniques. Each work however follows a different path to evaluate their proposed techniques. Despite the practical significance of optimizing UDF queries, UDFs have not been so far the focus of the database benchmarks. In this paper, we present UDFBench, a UDF-centric database benchmark based on real-world schema and data. We identify the core overheads in UDF query execution and design the UDFBench UDFs and queries to enable experimentation with these overheads, alone or in tandem with others. Finally, to showcase the portability and scope of UDFBench, we present an experimental analysis on five popular databases with different characteristics.

Modern data applications in areas such as text mining, document analysis, and data science, involve complex algorithms and logic that cannot be expressed in SQL. Therefore, SQL databases employ user-defined functions (UDFs) to extend their supported functionality. However, this comes at a significant performance cost as UDFs routinely become the bottleneck in query execution. To deal with this problem, we present QFusor, an optimizer plugin for UDF queries in relational databases. QFusor minimizes the performance overheads introduced by the impedance mismatch between the UDF and SQL execution environments by employing techniques such as vectorization, parallelization, tracing JIT compilation, and operator fusion for various types of UDF (scalar, aggregate, table UDFs) and relational operators. QFusor follows a pluggable, engine-agnostic design and can work with several popular SQL databases offering a significant boost in their UDF query performance.

Modern data management applications involve complex processing tasks over large volumes of data. Although this falls naturally within the scope of relational databases, many such tasks cannot be expressed in SQL and require additional expressive power achieved via user-defined functions (UDFs). However, efficient processing of UDFs in data engines hinge on dealing with the impedance mismatch between UDF execution and SQL processing. In recent years, the problem of efficient UDF execution in modern data engines has gained significant traction. In this tutorial, we present recent advancements in this area, involving a broad scope of solutions ranging from algebraic, cost-based optimization to low level, physical query optimization, compilation, and execution. We also describe limitations and open issues, and discuss promising future research directions.

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.

The diversity and complexity of modern data management applications led to the extension of the relational paradigm with syntactic and semantic support for User-Defined Functions (UDFs). Although well-established in traditional DBMS settings, UDFs have become even more central in many applications spanning data science, data analytics, etc. Still, a critical limitation of UDFs, which to some extent has turned data scientists towards NoSQL systems, is the impedance mismatch between their evaluation and relational processing. We present YeSQL, an SQL extension with rich UDF support along with a pluggable architecture to easily integrate it with either server-based or embedded database engines. We currently support UDFs written in Python, which are fully integrated with relational queries as scalar functions, aggregators, or table returning functions. Key novel characteristics of YeSQL include easy implementation of complex algorithms, tracing JIT compilation of Python UDFs, and seamless integration with a database engine. Our demonstration will showcase (a) the usability and expressiveness of our approach, and (b) that our techniques of minimizing context switching between the relational engine and the Python VM are very effective and achieve significant speedups in common, practical use cases.

The diversity and complexity of modern data management applications have led to the extension of the relational paradigm with syntactic and semantic support for User-Defined Functions (UDFs). Although well-established in traditional DBMS settings, UDFs have become central in many application contexts as well, such as data science, data analytics, and edge computing. Still, a critical limitation of UDFs is the impedance mismatch between their evaluation and relational processing. In this paper, we present YeSQL, an SQL extension with rich UDF support along with a pluggable architecture to easily integrate it with either server-based or embedded database engines. YeSQL currently supports Python UDFs fully integrated with relational queries as scalar, aggregator, or table functions. Key novel characteristics of YeSQL include easy implementation of complex algorithms and several performance enhancements, including tracing JIT compilation of Python UDFs, parallelism and fusion of UDFs, stateful UDFs, and seamless integration with a database engine. Our experimental analysis showcases the usability and expressiveness of YeSQL and demonstrates that our techniques of minimizing context switching between the relational engine and the Python VM are very effective and achieve significant speedups up to 68x in common, practical use cases compared to earlier approaches and alternative implementation choices.

State-of-the-art OLAP systems tend to use columnar data representations, as these are both suitable for analytics and amenable to compression. Local dictionary value encoding has been shown to achieve high compression rates for string columns while still allowing fast filtered scans. In this paper, we argue that the effectiveness and efficiency of local dictionary compression is limited by data repetition across file blocks and by dictionary look-ups inside each block during filtered scan execution. To address this problem, we introduce an adaptive compression technique that is based on differential dictionaries and targets both storage efficiency and query performance. The proposed scheme reduces dramatically the need to store repeated values across different file blocks and significantly accelerates read operations by reducing the time needed for dictionary look-ups. A preliminary set of experiments has given very promising results, showing that, in many cases, the proposed new dictionary compression scheme is much more efficient than existing techniques, occasionally up to an order of magnitude.