BlackMagic: Automatic inlining of scalar UDFs into SQL queries with froid

Karthik Ramachandra, Kwanghyun Park

    Research output: Contribution to journalConference articlepeer-review


    Relational DBMSs allow users to extend the standard declarative SQL language surface using User Defined Functions (UDFs) that implement custom behavior. While UDFs offer many advantages, it is well-known amongst practitioners that they can cause severe degradation in query performance. This degradation is due to the fact that state-of-the-art query optimizers treat UDFs as black boxes and do not reason about them during optimization. We demonstrate Froid, a framework for optimizing UDFs by opening up this black box and exposing its underlying operations to the query optimizer. It achieves this by systematically translating the entire body of an imperative multistatement UDF into a single relational algebraic expression. Thereby, any query invoking this UDF is transformed into a query with a nested sub-query that is semantically equivalent to the UDF. We then leverage existing sub-query optimization techniques and thereby get efficient, set-oriented, parallel query plans as opposed to inefficient, iterative, serial execution of UDFs. We demonstrate the benefits of Froid including performance gains of up to multiple orders of magnitude on real workloads. Froid is available as a feature of Microsoft SQL Server 2019 called 'Scalar UDF Inlining'.

    Original languageEnglish
    Pages (from-to)1810-1813
    Number of pages4
    JournalProceedings of the VLDB Endowment
    Issue number12
    Publication statusPublished - 2018
    Event45th International Conference on Very Large Data Bases, VLDB 2019 - Los Angeles, United States
    Duration: 2017 Aug 262017 Aug 30

    Bibliographical note

    Publisher Copyright:
    © 2019 VLDB Endowment.

    All Science Journal Classification (ASJC) codes

    • Computer Science (miscellaneous)
    • Computer Science(all)


    Dive into the research topics of 'BlackMagic: Automatic inlining of scalar UDFs into SQL queries with froid'. Together they form a unique fingerprint.

    Cite this