Froid: Optimization of Imperative Programs in a Relational Database

Proceedings of VLDB | , Vol 11(4)

For decades, RDBMSs have supported declarative SQL as well as imperative functions and procedures as ways for users to express data processing tasks. While the evaluation of declarative SQL has received a lot of attention resulting in highly sophisticated techniques, the evaluation of imperative programs has remained naïve and highly inefficient. Imperative programs offer several benefits over SQL and hence are often preferred and widely used. But unfortunately, their abysmal performance discourages, and even prohibits their use in many situations. We address this important problem
that has hitherto received little attention.

We present Froid, an extensible framework for optimizing imperative programs in relational databases. Froid’s novel approach automatically transforms entire User Defined Functions (UDFs) into relational algebraic expressions, and embeds them into the calling SQL query. This form is now amenable to cost-based optimization and results in efficient, set-oriented, parallel plans as opposed to inefficient, iterative, serial execution of UDFs. Froid’s approach additionally brings the benefits of many compiler optimizations to UDFs with no additional implementation effort. We describe the design of Froid and present our experimental evaluation that demonstrates performance improvements of up to multiple orders of magnitude on real workloads.

Harmonizing the declarative and imperative in database systems

Most relational database engines allow users to express their intent with both declarative SQL and imperative functions/procedures, and practitioners often combine the two in database applications. But while today’s database systems employ highly sophisticated techniques to optimize and evaluate declarative SQL statements, the evaluation of imperative programs has largely remained naive and inefficient. This has limited their use in many performance-critical situations despite imperative programming offering several benefits over SQL. In this webinar, Karthik Ramachandra, a Principal Engineering Manager who heads the Azure SQL Database R & D India organization at Microsoft, will take you on a journey addressing this important but often overlooked problem. First, he’ll describe how the declarative and imperative styles of programming are intertwined in today’s database systems and explain the challenges faced by practitioners. Then, he’ll address why this problem has lingered without a solution for years and what makes it unique. He’ll also delve into the details of Froid and Aggify, two related techniques for evaluating imperative programs in database systems that result in performance improvements of up to multiple orders of magnitude over the existing state of the art. Together, you’ll explore: The interplay of declarative and imperative styles of programming in database systems The root cause of the performance disparity between these two styles The recent developments with Froid and Aggify, which blend technologies from compilers and database query optimizers seamlessly to get the best of both programming styles An experimental evaluation that demonstrates how these techniques harmonize the two disparate programming styles for a large class of database applications Resource list: Froid homepage Aggify homepage Optimizing imperative functions in relational databases with Froid (Blog) Introducing Scalar UDF Inlining (Blog) Froid and the relational database query quandary with Dr. Karthik Ramachandra (Podcast) Froid: Optimization of Imperative Programs in a Relational Database (Paper) BlackMagic: Automatic Inlining of Scalar UDFs into SQL Queries with Froid (Paper) Aggify: Lifting the Curse of Cursor Loops using Custom Aggregates (Paper) Invited talk at University of Washington, Seattle, March 2019 *This on-demand webinar features a previously recorded Q&A session and open captioning. Explore more Microsoft Research webinars: