BlackMagic: Automatic Inlining of Scalar UDFs into SQL Queries with Froid

被引:8
|
作者
Ramachandra, Karthik [1 ]
Park, Kwanghyun [2 ]
机构
[1] Microsoft Res India, Bengaluru, India
[2] Microsoft Gray Syst Lab, Madison, WI USA
来源
PROCEEDINGS OF THE VLDB ENDOWMENT | 2019年 / 12卷 / 12期
关键词
D O I
10.14778/3352063.3352072
中图分类号
TP [自动化技术、计算机技术];
学科分类号
0812 ;
摘要
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 multi-statement 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'.
引用
收藏
页码:1810 / 1813
页数:4
相关论文
共 8 条
  • [1] Implementation of Projected Clustering based on SQL queries and UDFs in Relational Databases
    Harikumar, Sandhya
    Haripriya, H.
    Kaimal, M. R.
    [J]. 2013 IEEE RECENT ADVANCES IN INTELLIGENT COMPUTATIONAL SYSTEMS (RAICS), 2013, : 7 - 12
  • [2] Automatic Generation of SQL Queries
    Do, Quan
    Agrawal, Rajeev K.
    Rao, Dhana
    Gudivada, Venkat N.
    [J]. 2014 ASEE ANNUAL CONFERENCE, 2014,
  • [3] Unnesting scalar SQL queries in the presence of disjunction
    Brantner, Matthias
    May, Norman
    Moerkotte, Guido
    [J]. 2007 IEEE 23RD INTERNATIONAL CONFERENCE ON DATA ENGINEERING, VOLS 1-3, 2007, : 21 - +
  • [4] Answering complex SQL queries using automatic summary tables
    Zaharioudakis, M
    Cochrane, R
    Lapis, G
    Pirahesh, H
    Urata, M
    [J]. SIGMOD RECORD, 2000, 29 (02) : 105 - 116
  • [5] A Novel System for Automatic, Configurable and Partial Assessment of Student SQL Queries
    Fabijanic, Mario
    Dambic, Goran
    Fulanovic, Bojan
    [J]. 2020 43RD INTERNATIONAL CONVENTION ON INFORMATION, COMMUNICATION AND ELECTRONIC TECHNOLOGY (MIPRO 2020), 2020, : 832 - 837
  • [6] Automatic Whitelist Generation for SQL Queries Using Web Application Tests
    Nomura, Komei
    Rikitake, Kenji
    Matsumoto, Ryosuke
    [J]. 2019 IEEE 43RD ANNUAL COMPUTER SOFTWARE AND APPLICATIONS CONFERENCE (COMPSAC), VOL 2, 2019, : 465 - 470
  • [7] Automatic evaluation of SQL queries in virtual environment for teaching-learning
    Lino, Adriano D. P.
    Silva, Aleksandra S.
    Favero, Eloi L.
    Brito, Silvana R.
    Harb, Maria da Penha A.
    [J]. NOVAS PERSPECTIVAS EM SISTEMAS E TECNOLOGIAS DE INFORMACAO, VOL I: ACTAS DE 2A CONFERENCIA IBERICA DE SISTEMAS E TECNOLOGIAS DE INFORMACAO, 2007, 2007, : 89 - 100
  • [8] Preventing SQL injection attacks by automatic parameterizing of raw queries using lexical and semantic analysis methods
    Samarin, S. Dolatnezhad
    Amini, M.
    [J]. SCIENTIA IRANICA, 2019, 26 (06) : 3469 - 3484