skip to main content
10.1145/3475726.3476451acmotherconferencesArticle/Chapter ViewAbstractPublication PagessplashConference Proceedingsconference-collections
invited-talk

Unified Optimization of Declarative and Imperative Code in Relational Databases: (Invited Talk)

Published: 03 October 2021 Publication History

Abstract

In relational databases stored procedures and user defined functions (UDFs) have been used to express application business logic by using control flow logic and DMLs. Recently their use is increasing with the rising popularity of real-time analytics applications since the applications often contain complex business logic. Thus hybrid transactional and analytical processing systems like SAP HANA have started to put more efforts to optimize the execution of UDFs.
There has been much work in the fields of program optimization and query optimization, respectively. However, most of the studies have been done separately in isolated worlds, and cross-optimizations breaking the boundary between declaratives and imperatives were not studied enough. Therefore, unified optimization techniques considering both program and query optimization techniques are essential for achieving optimal query performance.
The first part of this talk presents a literature overview of a successful approach to improve the performance of UDF execution. This approach transforms entire UDFs into equivalent relational algebra expressions, then applies existing relational query optimization techniques. It is interesting that the imperative constructs such as branches and loops can be converted into relational algebra expressions. This approach is attractive since we can simply take advantage of existing sophisticated query optimization techniques after the transformation. However, one of the most challenging issues faced by this approach is that transformation rules are currently limited and there are non-transformable imperative expressions.
The second part presents our past and ongoing work using another approach, which unifies both program and query optimization techniques in a framework. The framework consists of plan enumeration and cost estimation for the UDFs. We first demonstrate this approach with iterative query processing in a UDF. With the notion of query motion, by which an SQL query is moved in and out of a loop, we enumerate execution plans for the UDF. We choose the best plan by using a cost model which measures the procedure cost based on the cost estimation of the query optimizer and imperative constructs. We next discuss unified optimization using the concept of relational operator motion, where we pull up or push down relational operators among statements in the UDF to obtain a globally optimized plan of the UDF.

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM Other conferences
DBPL '21: The 18th International Symposium on Database Programming Languages
August 2021
27 pages
ISBN:9781450386463
DOI:10.1145/3475726
Permission to make digital or hard copies of part or all of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for third-party components of this work must be honored. For all other uses, contact the Owner/Author.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 03 October 2021

Check for updates

Author Tags

  1. declaratives
  2. imperatives
  3. query optimization
  4. stored procedures
  5. unified optimization
  6. user-defined functions

Qualifiers

  • Invited-talk
  • Research
  • Refereed limited

Conference

DBPL '21

Acceptance Rates

Overall Acceptance Rate 10 of 15 submissions, 67%

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • 0
    Total Citations
  • 35
    Total Downloads
  • Downloads (Last 12 months)7
  • Downloads (Last 6 weeks)0
Reflects downloads up to 16 Jan 2025

Other Metrics

Citations

View Options

Login options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

HTML Format

View this article in HTML Format.

HTML Format

Media

Figures

Other

Tables

Share

Share

Share this Publication link

Share on social media