Harmonizing the declarative and imperative in database systems

Key Takeaways

welcome to this webinar titled harmonizing the imperative and declarative in database systems i am karthik and i currently head the azure sql r d organization in india i have been with microsoft for about six and a half years starting off as a scientist at the microsoft gray systems lab in madison wisconsin followed by a couple of years at microsoft research india before taking on my current role from a research standpoint among other topics i've been interested in this intersection of database systems and compilers since my grad school days at iit bombay and i continue to work in this area and with this brief background i'll now jump into the topic of this webinar which is about harmonizing the declarative and imperative in database systems so i usually i would like to start by telling you a tale of two engineers in an e-commerce company so there is a so e-commerce companies usually have large data sets uh about their customers and the orders that they place over time and there is this e-commerce company fictional e-commerce company with millions of customers and billions of orders that they have placed right and there is a manager who owns this data and who is responsible for some for doing some analytics on this on these data sets and there are there are a few engineers developers in the team and one one day she comes up with a new requirement saying that they want to start a rewards program or a loyalty program and they and they want to categorize their customers into three categories platinum gold or regular and the and the logic for doing this is fairly simple if the orders made by a customer exceeds 1 million usd let's say they are categorized as platinum between 500k and 1 million they are categorized as gold and the rest are regular customers right so it's a fairly simple requirement and the manager wants the wants the engineers to basically implement this now it turns out that these two engineers have a disagreement about how to go about implementing this so they they go about implement implementing it in their own way so one of them happens to be an expert in sql so he goes about and writes writes a single complex sql query uh it's okay if you're not able to read this this query in detail we'll get to it later but basically the the point is that the sql expert was able to write encode this entire requirement into one single sql query whereas the other engineer who is more of an imperative programmer writes a simple sql query which calls into this function called service level which is a user defined function or a udf and for every customer key in the database this udf is going to compute what is their level or the category you will and as you can see the code on the right hand side uses what we call as imperative constructs such as variables and if conditions and and things like that combined with sql queries so the programmer in his mind is thinking that his program is better than the sql query for the following reasons because that you know the user defined function is modular you know it's encapsulates the logic inside a function and it is hence reusable it can be called from different queries and some tasks like these are easier to express and even for others to read and understand when it's expressed in the form of an imperative udf uh you know things like business rules like we are talking about here so that's the intent behind the programmer's decision to implement it this way now they come back to the manager with their respective programs and they say that hey look we have two different solutions you know you can pick and choose now so it turns out that after after doing some testing the ma okay yeah the both these engineers are hoping for a promotion let's say right this is a fictitious company so i can cook up the story and make it more uh you know interesting so both the engineers are hoping that they have done the best best implementation and they should get promoted but but what happens is uh it turns out that one of them will be promoted and the other one will be fired again fired is very dramatic but still it's okay for the purposes of this driving the point so i'll go ahead and tell you who was promoted and who was fired it turns out that the the engineer who wrote the complex sql query was indeed promoted and the and our friend who wrote a user defined function in the imperative paradigm was actually fired so the the question is why did this happen and the simple answer is that the manager basically asked them to run their programs on their millions of customers and billions of orders data and it turned out that the sql query ran well within under two minutes whereas the the user defined function took a few hours to execute and that was not at all acceptable for the business so they had to you know throw away that solution and take the sql query although both of them gave exactly the same results and there was they were pretty much identical in terms of the functionality so the bottom line here is that the benefits of these these imperative user defined functions uh today come with a huge performance penalty and this is true for pretty much all popular relational database systems today with that uh background story i'll now come and explain to you what's this interplay of declarative and imperative in the context of database systems for decades database systems have been exposing a declarative sql interface to users so so users can write sql queries and and the database engine figures out the most efficient way or you know optimal ways to evaluate those queries over large data and that has been the main focus of the database community over the last few decades but it turns out that in real workloads very often we do see because of user preferences and things like what we remember what we mentioned earlier in terms of modularity reusability and readability and such people do write or prefer to write user defined functions road procedures etc using imperative programming styles and they do execute or they do run inside of a database engine and they follow the imperative programming model or the paradigm it turns out that this has not received as much attention in the research community although there are there have been some really good uh works that have uh uh that have happened uh and that will be this will be our focus for this uh talk uh about the this how we bridge this imperative and declarative uh impedance mismatch as it is popularly called and in this to this end i'm going to talk about two techniques that we have built called the freud and agify which bridge this gap with that i'll uh i'll start talking about freud first this was a work that appeared in in the vldb conference in 2017 so let's come back to the story and continue it right so the engineer who was fired is still wondering what happened and why did his program run so slowly and why did it what was the reason it was performing so badly so it turns out that there are a few reasons why this happens and first of all it turns out that query optimizers today in relational database systems treat such user defined functions or imperative programs as black boxes in other words they do not look inside these functions during query optimization not just that they do not even cost these udfs usually query optimization is a cost based process where each operation is costed and the least cost operations are chosen it turns out that query optimizers do not cost udfs at all so we may have have a thought that you know why not execute these udfs in parallel uh while it is possible it is uh not straightforward at all especially in the presence of uh correlated queries like uh the one the query that is shown in in the middle of this here in the in inter intermixed with the imperative programming style there is a there is a declarative sql query inside the udf as well so this makes it harder to parallelize so as a result complex udfs like these lead to what we call as iterative or r bar execution r bar stands for row by agonizing row where you know what happens is for every tuple in this in this example for every row in the customer table the the function is called and it executes this particular query for every row essentially so which is actually very very inefficient and and that's what i mentioned here when i say due to an implicit join especially when these things become worse so here in fact an expert can see that this is actually a join operation that is happening between the customer and the orders tables which is the kind of via the function invocation so as a result it's not a directly expressed explicit join but implicitly that is what is happening here and the other problem finally is that these user defined functions and procedures execute in an interpreted manner so there is no cross statement optimization that is happening here that is another reason so there are all these reasons put together udfs and imperative programs in general are performed poorly so the next logical question that you might want to ask is all right if this was a problem why was this not addressed till now so it turns out that you know these user defined functions as i showed in the in the example they contain imperative constructs but they are executing in a database engine and they are similar to nested sub queries as we call it in the database world in some sense this is actually that's why this is actually an interdisciplinary problem which involves concepts from database systems and sql queries as well as programming languages and compilers so since this has been kind of in the boundary of these two areas it has sort of received little attention from either communities of the databases as well as programming languages however i must say that things have picked up recently there is a lot more interest and there are a lot of interesting work that that is coming out in this space so this the key problem here is what is called as the imperative versus relational or imperative versus declarative impedance impedance mismatch and what that means is that on one side we have declarative sql which is the focus of the database community and it turns out as a result that all the standard benchmarks that databases use do not use do not include any imperative programs at all so that shows that the focus has been towards declarative sql alone whereas on the other side you can also argue that already there are there are known compiler techniques why not just implement them for these udfs but it turns out that that is not sufficient because as i mentioned in the previous slide you know known techniques for compilers cannot i cannot identify implicit relational operations hidden inside through imperative code so as a result you know as a result of this mismatch that has existed for many years practitioners have had to make a choice right have to make a trade-off if they want the flexibility of udfs and the readability and the modularity they need to trade off performance and if they really want performance the recommendation is to not use udfs at all so it's kind of a tough trade-off to make very but uh practitioners have been giving feedback about the importance of this problem there have been many articles blog posts and feedbacks that that that are out there which talk about the pain of using uh procedural code inside a relational database so with with that now i'll move on slowly towards the background and intuition towards how we built how freud addresses this problem so jumping into uh there are a couple of observations that are that we need to uh get out of the way first so uh you know the first one is that iterative execution of udfs or this r bar execution is is similar to what is known as the correlated execution of nested sub queries now i'll explain this with an example so basically let's consider this sql query here shown in green and blue which is basically trying to find the the minimum cost suppliers for each part in the database right so here there is a sub query the the query in blue is is called as a nested sub query whereas the query in green is the outer query and there is what is there is a parameter from the outer query here is p1 dot part key which is passed as a parameter to the inner query and that is what is called as a correlating parameter from the outer to the inner query so for every tuple in the outer query this query essentially says that you execute this inner query using this as the parameter and take the result of that query and then subsequently evaluate the outer query so this is this is what is called as a correlated execution of nested sub queries so moving on to the next observation although udf's execution of udfs is similar to this nested sub queries right turns out that for uh optimizing sub queries rdbms's have evolved mature techniques it's it's it's going to do a lot of you know rewrites to to optimize such queries to run efficiently and one such example is the is the apply operator which is used in microsoft sql server which was introduced in sigmoid way back in 2001 where correlated sub queries are modeled using the apply operator this is the formal definition of the apply operator but i can i'll walk you through using this example essentially what this says is that for every tuple t right in the expression e naught right this is the outer query essentially that's why i have highlighted this in green for every tuple t in expression e naught what we need to do is evaluate the parameterized expression e one which is the sub query that's why i'm showing it in blue right parameterized by t and then combine e naught and e one of t using a join type shown using this symbol and the joint type could be cross product inner join outer join etc so showing it in a tree form for this the same example this query when modeled using the apply operator will look like this there are two references to the part supplier table as you can see there's a blue one and there's a green one and this whole part the right side of this apply operator is actually the sub query or the blue part of the query which is actually using this green parameter and executing this operations and then it is being joined using the apply with the cross product here as i mentioned and then it performs a filter and and projections and basically this is how the query gets modeled using the apply operator now once once once we model a query using a sub query using an apply operator there are algebraic rules which can be used to simplify this and and you can in fact remove the apply operator and introduce standard relational operators in in its place so for example taking the same tree that i showed in the previous slide this is this is basically correlated execution modeled using the apply operator and an optimizer can actually rewrite it into the form in the right which is basically transformed it into a set-oriented join operation here which is doesn't have to execute in an iterative manner this is a standard known optimization for nested sub queries so the the key intuition here is that the same technique can be extended to udf invocations as well and this insight was actually shown earlier in a paper in icde 2014 titled decoration of udf invocations in queries and freud basically follows this intuition so to summarize the intuition all we are trying to say here is that if the entire body of a of an imperative user defined function can be expressed as a single relational expression or a single query then any query that calls this udf can now be written as a query with a nested sub query and then we can leverage all the existing optimizations for sub queries that already exist so the key challenge now becomes how do you express the body of an imperative udf as a single relational expression so i'll now walk through using an example uh of how we can open this black box udf on the left side in a systematic way and come up with a with a equivalent relational algebraic expression or an sql query so the first step here is we have a way to ma to express or translate imperative statements to equivalent sql expressions so for example a simple variable assignment can be seen as a projection as a constant projection uh as a variable assignment with this with an sql query can be seen as a sub query here with this case that's a scalar sub query projecting a column and here i'm showing the relational algebra form of the same sql query and we can do a similar translation for if else kind of constructs by using the case statement in relation in sql so you might have a question about the other commonly used construct is the looping construct i will come to that later that's the second part of the talk but for now we'll stick with these constructs so once we have relational expressions for individual statements what we do is we divide this program into what we call as regions again these are known techniques in compilers where they use what we call as region based analysis ah so this is a sequential region as well as a line of some sequential straight line code and we we can combine the individual expressions we came up with in the last slide and come up with one single expression corresponding to this block of code and similarly for the second block we come up with an equivalent sql expression and similarly for the third block for the return statement i will show it what happens in the next slide but essentially here what we have is three sql queries if you will which are equivalent forms of these corresponding regions once we have these we can we we show that you can actually merge these expressions using the apply operator uh for example here uh this this this total and level this relation here this corresponds to the block one and this corresponds to block two the the columns here represent the variables that are assigned in this particular region so all we do is we combine these two using a apply operator like this similar to a join and similarly we combine the level the third block as well and at the end for the return statement we just put a projection operation which projects out the corresponding column which is level in this case in algebraic form this is how you you would write this and in an sql form this is how you would write this where you are just selecting the final column from region 1 cross apply region 2 cross apply region 3. so this is now one single sql query which is equivalent to this imperative program so now we have essentially translated this declarative or this imperative form on the left to the declarative sql form on the right note that this query that i am showing on the right is actually simplified there is more more details which which is which i cannot show in the presentation but i am happy to discuss offline once we have this this expression all we do is we look at the query that is calling this function and in place of the function we just put the entire the big sql query that we came up with right which we came up with in the last slide now this might look very look like as though we made the query more complex than it was before which is true from a human point of view as a human this query is much more complex than this one but uh but for an optimizer for a database query optimizer this is actually a language that it can understand very very well so in fact this has opened up this black box and expressed all the operations inside it in a single sql query so all we do is at this point we we give this input instead of the left hand side here we give the this right hand side as the input to the query optimizer and let it do its magic and as as i was mentioning the query optimizer is very good at optimizing sub queries so for this particular example here the query optimizer comes up with a query plan or a strategy to execute it which is identical to the one i'm showing on the right if you remember carefully this was indeed the query that the sql expert had written handwritten so we have essentially kind of automated or found a systematic way to translate this imperative style of programming into a form that the query optimizer can understand and optimize so let's see why this query will now perform what are what are the benefits of this query on the right side as we as i mentioned there was an implicit join on the left side which has become now expressed explicitly here as a left outer join and also there was an implicit group by here customer which which also is made made explicit here which makes it easier for the query optimizer to come up with better efficient ways to execute it and now that there are no hidden operations or black box operations all the computations that are happening in this query are available to be costed and made make a cost based decision and this query is now parallelizable because the the databases are aware of how to parallelize these kind of queries and there are no function call overheads as i was mentioning here there's one function called per row whereas here there is no such function called overheads so these are all the benefits that we get and and and also the interesting thing is that we we can make this happen without changing the query optimizer itself which is actually really important in a real production database engine where changing query optimizers is actually a very risky proposition so just to complete the story with this now our programmer can also be happy because his query will also run as efficiently as the one that was hand written by the sql expert so there is also a bonus here not just a promotion so you we actually get a bunch of compiler optimizations for free by doing this kind of translation and i'll just walk through with a simple example this is a udf that takes in a parameter and based on some simple conditional logic it returns you know a string which is either says high value or low value it's a simple toy example so now what freud would do or the the transformation that i just talked about will rewrite or transform this into this form right where it has expressed this using this apply operator as i was explaining in the past now i'll show two things if this imperative program was directly given to a compiler of an imperative language it would perform certain optimizations i will show that here and i'll show how freud will achieve the same optimizations automatically using relational algebra transformations so first of all there is a standard technique called dynamic slicing uh if if the parameter for this particular function is a constant or is known at compile time you could actually eliminate the if conditions and and make the program simpler and it turns out that if we give this same program to a database engine it can also uh perform a similar transformation or simplification here which achieves the same outcome as dynamic slicing and then this can be further given to a technique called constant propagation and folding because some of these are compile time constants and they can be substituted directly as we are showing here in the return statement the the string append and can be done at compile time itself and it turns out that similar things happen even in a database engine once we express it in this form and finally there is also a common optimization called dead code elimination where some lines of code are dead or not not used at all don't influence the return value so they can be removed and it turns out that the database engine can do just do just the same thing using standard relational optimizations so just by doing this arrow mark that i'm showing here using lo right just by doing this we got all these things for free right these things are already there but they were they they were not because this was a black box earlier none of those optimizations would happen but now because we just opened it up we are enabling all of those optimizations so you know moving on freud currently supports a bunch of these operations which is basically variable assignments queries if else conditional logic and nested function calls recursive function calls return statements and see various sql data types etc whereas again let's come back to this question that i had mentioned earlier what about looping constructs right so uh for that uh this was this was one key drawback in the in the freud uh first you know paper that we had and so we followed it up with uh with a technique called agify which i will talk about next uh this appeared as a paper in sigmod this year earlier this year so just to give a background about cursor loops or basically these loops are a common variant of loops which uh basically iterate over the result set of a query and perform some computation on each row let me give an example so what we want to do is given a part key right a part id identifier we want to find us the supplier name who supplies this part at a minimum cost this was similar to the query that i had shown in the in the earlier slide this is adapted from a popular database benchmark so using a cursor query using cursor loops you can write it this in this way so first you can declare a cursor for this query which gives out all the supplier names and the cost at which they supply the part and then followed by a loop which iterates over each row of this results of this sql query and then computes the minimum cost and also computes the name of the supplier who is supplying it at this minimum cost right this is a simple loop note that if you the other way of expressing this logic using is using sql sub queries as i mentioned in a previous example and not not all programmers who would be able to write that and again this is a simplified example real world cases could be much harder so people there are a lot of developers who prefer to write loops over results and express logic in this form whereas whereas writing sql sub queries and complex sql queries requires some expertise in sql but however you know if you ask an sql expert they will always say that you know it's a pain to uh you you should avoid writing code like this because uh of what is called as the curse of cursors uh which is basically you know the problem with cursors is that the following that in many cases cursors end up materializing the query results or storing the query results on disk which results in additional i o and memory overhead and these loops are executed in an interpreted form as i was mentioning earlier which is again result it makes it slower and if if this loop for example happens to be in a remote application program that is connecting to the database using apis such as jdbc or ado.net this would result in large data movement from the database server to the client because the loop logic is actually happening in the client despite this we have you know we have noticed or observed that such loops are very common in real workloads people prefer to write it because it's very easy and intuitive and it has a lot of other advantages uh we have done a bunch of uh you know analysis on the real and open source workloads uh which prove this point so basically that led us into uh you know lifting this curse which is basically the objective of agify is to optimize cursor loops such that developers can continue to reap the benefits without worrying about performance again it follows the same theme as freud but extending it to cursor loops so again the intuition comes from the icd 2014 paper by simahadri and others that the operations in a cursor loop can be captured using custom aggregates now quickly a quick background here an aggregate function in databases is is one that performs a computation on a set of values and returns a single value for example a sum min max and such operations which operate on an on a set of values and gives out a single value as an output such are called as aggregate functions and custom aggregates are are those that encode user defined logic rather than standard operations like sum min or max you may do custom logic in there so the intuition says that you know operations in a cursor loop can be expressed using custom aggregate and once we do that we can transform the entire cursor loop into an sql query that invokes this aggregate now let me again summarize this algorithm and give you a given example to walk through so agify basically accepts a cursor loop as input which satisfy certain preconditions i will come to it later and as a first step it constructs systematically constructs a custom aggregate that expresses this loop logic and then it can it then follows and constructs an sql query that invokes this generated aggregate so step one is to construct an aggregate for a loop uh so the custom aggregates generally follow a contract which which includes three functions that need to be implemented called init accumulate and terminate so here i have a template of of an aggregate as you can see this is a class that implement that has three functions in it accumulate and terminate and the body of these or the contents of these the class and the functions are shown using a template using light blue color uh this is the this is basically the blanks that we need to fill by looking at this loop so for example the the the loop is shown on the left and left hand side bottom of the slide and for for such a loop now we want to we want to analyze this loop and construct a custom aggregate that is shown on the right which is equivalent in which expresses this loop logic so i'll just quickly uh jump to how this what what how we like if i would result in generating this aggregate so for example in this case it would figure out that you need four fields in this class and it determines what should go into the init definition and what should be the parameters that you pass to this function you know and what should be the body of this function and similarly what should be in the terminate function and what should be the return type and so on so now this is auto generated by agify given the loop on the left the the key thing i want to focus your attention to is the green highlighted part which is basically showing that the core logic of the loop body is pretty much identical and it's just placed in the accumulate function of this custom aggregate so this makes it fairly generalizable to all kinds of logic that people may write inside loops you could you could if the preconditions are satisfied you could basically map them onto a custom aggregate function so once we do this we all we have to do is uh write a sql query which will invoke this aggregate so this is the input cursor loop that i am showing on the on the left and on the right i am showing the output of agify which is the transform which is applied which is the arrived at using a transformation rule which is described in the paper along with the proof of its correctness and so on but again i would like to highlight here that you know the yellow box if you see is the query that was used for the cursor loop and that has in fact become the part of the from clause of this rewritten sql query identically right so it's exactly the same thing so it's very uh straightforward i mean non-intrusive we you don't have to do too many changes to the code right it can retain the query as is and the body of the loop is just now encompassed inside this loop aggregate aggregate custom aggregate function right so now this entire loop has been replaced by a single sql query which computes the exact same result so with this what are the benefits we get right as was mentioned earlier the curse of materialization of disk is no longer applicable this this single sql query can run in a completely pipeline fashion and instead of statement by statement interpreted execution this aggregate function can now be compiled and invoked from a sql query which makes it more efficient and in the case of remote applications the large data moment is avoided because now the entire loop body loop logic is also the computation is pushed into the database which makes it closer to the data avoiding the moment in addition to all these benefits there are again a lot of bonuses in the fa in the sense that since we use custom aggregates there is there's a lot of earlier work on aggregate functions optimization in database literature and all those will be applicable here so that makes the that can lead to further improvements and as you might have observed this technique will integrate seamlessly with freud which i mentioned earlier if a udf has a loop in the middle along with other statements agify can replace the loop with a single sql query and then freud can be used to optimize the entire udf uh and there are also other related works which which you know which can be integrated with agify uh easily and uh the final thing is that this is a fully automatic technique and it's non-intrusive as i was mentioning so that makes it uh fairly practical so now i'll move on to a quick evaluation uh of the of the freud and agify techniques so we have done some analysis on microsoft azure sql databases worldwide by taking 100 databases top 100 databases using that use the user defined for scalar user defined functions and in those we find we found that there were around 85 000 scalar udfs out of which freud was able to inline more close to 60 of them so which is a very fairly large applicability and also we picked a few customer workloads under nda for doing deeper analysis and these workloads wn w2 and w3 had a bunch of scalar udfs and it turns out that freud was able to inline more than 80 percent in in most of these so i'll just quickly show some performance improvements here so we we have done these experiments on on a machine on a fairly uh decent server machine and this experiment uses the tpch benchmark of 10 gb size using the warm buffer cache and here on the x axis i am showing queries that use udfs and on the y-axis i am showing the execution execution time in seconds in log scale so i'm going to show three bars here so first one is the original tpc h query right which had no udfs in it right the original query was run as is unmodified and this was the time taken and we what we did is we rewrote some of these queries to use user defined functions but still do exactly the same thing so the results will be identical but the query is expressed using user defined functions for certain parts of the query and as you can see the performance takes a huge hit for these queries just by using udfs but without using freud right we just express the same logic using udfs and this shows what is the overhead of using udf today you know without techniques like freud and once we enable freud on this on this queries you see that the performance comes back pretty much almost to the original query so the so with freud the overhead of introducing udfs is practically zero in most cases so users essentially achieving this goal of giving the flexibility to users to you to write udfs without having to worry about performance now they can choose to write either complex equal queries or udfs but still get performance so so achieving this flexibility without trading off performance was was the goal that we wanted to achieve here and this is a summary graph that shows for two uh real workloads uh the improvements that we got so the x axis each bar in the in this graph shows one udf and and and the y-axis shows the factor of improvement that we get uh over the non-inline or the non-uh you know the original udf without using freud so we see orders of magnitude improvement and knowing the reasons it's not very surprising because we are now uh you know opening up this black box as i described earlier that leads to huge benefits i'll pick one of these examples one of these udfs and zoom into it and i'll show how the performance varies as the data size varies so here i'm going to take a simple query and on the x-axis i'm showing the size of the data or the size of the table which implicitly translates to the number of number of udf invocations in the query and the y-axis again shows the time taken in log scale so the first bar here this is showing the inline off case which means the when freud is turned off uh as you can see the performance degrades as the size of the data increases and with freud and this is the with freud and dop one by dop i mean degree of parallelism which means that here uh the the brown bar that i am showing is actually runs the freud optimizations but it disables parallelism just to identify what is the benefits of that we get additional benefits we get through parallelism so as you can see at higher cardinalities we get up to two orders of magnitude you know this is log scale so two orders of magnitude improvement reduction in time taken to execute it right and with parallelism as you can see we get an additional order of magnitude improvement at larger cardinalities and this is when the query optimizer prefers to choose a parallelizable plan because it it realizes that it is more efficient that way so this this so you are here you are able to see three orders of magnitude improvement due to freud uh this freud not only results in uh you know improvement or reduction in time it also reduces the resource utilization here i'm going to show a graph of cpu utilization uh we have taken some some uds 15 udfs from one of the customer workloads and the graph is fairly simple the y-axis shows the cpu time in seconds in log scale again and with freud off you see that the cpu time taken is much much higher and with with freud enabled it kind of we get again couple of orders of magnitude in most cases so i like to summarize now basically you know the the key takeaway if you will from this talk is that this imperative declarative interplay that i talked about is here to stay in the database systems world right i mean they appear in various forms in addition to what i mentioned with user-defined functions and stored procedures they are also widely found in what we call as object relational mapping tools and data wrangling scenarios and bi scenarios and so on so so this all these situations are places where users end up using a mix of imperative and declarative uh code so it is uh it is of importance and and this fact needs i i believe that it needs a broader acknowledgement from the uh research community and you know pretending that it doesn't exist is actually harmful because a lot there are a lot of developers and you practitioners out there who are affected by this and addressing this problem requires interdisciplinary research in this case uh company combination of database systems and programming languages and which is actually very exciting which is which what gets me interested in this topic because you know it kind of it allows you to kind of learn about diverse areas of research and bring them together into a into a common solution freud and agify are steps in this direction and they offer a unique practical approach to essentially harmonize the imperative and declarative paradigm seamlessly in database systems and this results as i mentioned to flexibility without the performance trade-offs that i conclude my talk and i can take questions hello everyone thanks a lot for attending this webinar on harmonizing the declarative and imperative in database system i'm karthik ramachandra and for the next 15 minutes i'm going to be here answering some of the questions that we have received from the audience uh i and again i would like to thank everyone for attending and also for submitting some really good questions i will try to address some of them in the next 15 minutes so one of the questions that has been asked is how can these techniques benefit data science and machine learning type of programs uh so that is uh that's a very good question uh you know uh as i mentioned in the in the final slide of my talk as well uh you know a lot large chunk of data science or machine learning involves processing the data and preparing it to be able to run the machine learning training or inference algorithms and this whole the processing and data preparation aspects are uh usually you know written in imperative programs in imperative languages like python but then in many cases the data itself resides in a in a database so usually the common pattern is that the python program would retrieve some data from a database and and then perform some uh data data transformations and data prep uh before uh doing uh training etc so again we have this as i mentioned it is this is a classic situation where there is a mix of uh you know imperative python core and also uh declarative sql queries within so this is again a a situation where techniques like floyd agify and others that have been proposed in this line of work uh can be applied or extended and leading to you know a good performance improvement i hope that answers the question uh uh there's another question uh there's another question about uh you know uh yeah one question that i liked was about the compilation based uh databases that was asked by somebody uh does this does this problem also occur in compilation based dbmss like hyper or umbra or is this an artifact of interpreted interpreted databases or interpretation based dbmss so uh yeah this is a good uh question again um uh you know i i i think i mentioned in one of my slides that you know there is a it is possible it can be argued that why not you know just apply standard unknown compiler techniques onto user defined functions and stored procedures and that is that is very much similar to what compilation based databases would dbmss would do today is that if you if you try to compile a user-defined function directly it would it would apply all the possible optimizations that a compiler would do so you would really you would definitely gain from it but then if there is a if there is an sql query that is invoking this particular udf uh very often uh that boundary still exists uh as a you know as a dll boundary or as a library separation itself so there is no cross optimization happening between the declarative and the imperative parts in many cases because because these techniques do not really try to you know identify or extract relational operations which may be embedded in the in the imperative side of things so although we do gain uh by doing compilation uh it is it doesn't uh address the iterative execution problem there is still iterative execution but faster iterative execution i would say we did in fact i didn't have time in the slides but we the paper uh contains more experiments that we have done um using uh by comparing it with these native compilation based techniques uh as well so i think there is a very interesting sweet spot here which we can exploit is that these two techniques which is compilation based uh optimize compilation for user defined functions and imperative code and freud style rewriting these two there is a way to you know you could actually use them together they need not be mutually exclusive um i i think you come by combining these two there is a possibility of getting the best of both so i see one more question uh does freud uh or agify always guarantee a better query plan without freud or ragified so by looking at the query execution time at least from the results it appears so but uh the the question is that uh have have we found any case where the plan picked up was actually worse than ah than the original plan so that's a great question again so there are always there are corner cases where doing these kind of optimizations might result in the engine picking up a plan that might take longer so for example uh you know as i mentioned this is mainly about replacing iterative execution with that oriented operators but if if we are dealing with for example very small data sets let's say right the overhead of doing this compilation or this project style transformation might sometimes uh you know overweigh the benefits that you get so for for small data sets for instance you don't see much benefits or another example could be cases where the bottleneck is really not the udf or the user defined function itself but let's say there is a really complex query which is doing you know complex joins and aggregations and the udf happens to be a really small part of the whole query there even by doing this this freud style optimization we may not really uh you know it may not really see any much benefits because uh the real bottleneck is elsewhere so uh so obviously yeah so these cases do exist although i must say that from our experiments and from looking at many of the real world customer workloads it's obvious that uh this is a minority i mean the these are outliers and for majority of cases we do see that the the plan picked up after applying these transformations uh usually ends up ends up better because because the optimizer just knows more info has more information about uh what is being asked for right uh it knows more about the operations that are happening inside the user refund function so it usually helps although there are uh cases like that that would be my answer there's there is one other interesting question uh wouldn't uh so wouldn't custom aggregates involve using imperative forms right so that's that's again so in in the agify technique i showed that we take a loop and translate it into or transform it into an aggregate function but as i had mentioned as i had shown in the slide the body of the loop is pretty much identically placed inside the aggregate function so in that sense you're right we are in that particular in that case we are not really translating or transforming that piece of imperative logic into a declarative form uh so that will still work run as an as a imperative code at execution time but uh having said that it is you know by doing this transformation we gain uh other benefits because now we are able to express the entire loop including the query and the loop body as one single sql statement and that gives us a lot of additional opportunities for optimization because now these statements can further be optimized by inlining with other uh you know uh in the context of a larger udf if you have a loop in within uh that can uh be still have a benefit so that that that i hope answers that question um some more questions so one more follow-up question by the person who had already asked before in case a plan is identified as bad does freud reverse back to the original plan or the old plan so that's a it's a tough question in some sense because how do you know that the plan is identified as bad i mean if you're if we discover that the plan is bad after execution that's too late so it is not always possible to to do that but yes we do have you know we do have some heuristics and some techniques where we where we basically identify the typical scenarios where bad plans could be chosen due to applying freud or agify and we make sure we we do not apply this optimization this is i mean just like any other optimization that compilers do there are always cases where the optimization results in a much slower program or whatever my suboptimal program sometimes so we can prevent most of those scenarios i see one more question what would speak against compiling only some udfs using freud while others remain as is and thus evaluated r bar do you propose to apply freud globally so yeah this is again a great question um so you know given in in any workload if you look at a real workload there are like you know there might be a bunch of user-defined functions that users have written and some of them might be candidates that could be optimized using freud and others may not be because of the way they are written or the many other there are some preconditions as i mentioned in the pa in the talk uh so in general for the workload doing as much as you can actually helps right optimizing those that can be and while other if there's a if there's an for example if there is a udf that calls another udf this is a nested uh function call uh those cases uh you know you may if we are able to in optimize one of them using freud but not the other that is still that will still lead to some benefits so so it is it is like you know it's like a best effort case where you try to optimize as much as possible but there will there might be certain limitations where you will not be able to apply this um so uh you know applying freud globally in my opinion we should but as i was mentioning there are corner cases as long as we take care of those and you know for example the other the other scenario here is that if your if the query is extremely complex and then the udf also happens to be extremely complex you could result in by applying freud you could result in a query that is like even more complex right so and uh and if if the query optimizer is uh not capable enough to uh digest that complexity and come up with a good plan there is a likelihood of uh ending up with a bad plan so uh these are more of you know implementation and systems challenges where we have to figure out like what's the right uh you know level of you know in using this optimization conceptually i would definitely say that we should try to apply it as wherever possible so there is uh i can probably take one last question before we wrap up there is one question uh [Music] about yeah so is it that every imperative form is convertible to a declarative sql query that's again a good uh good uh you know question that comes to mind when uh when we you know look at these kind of solutions uh uh conceptually uh you know sql with recursion is is during complete so it should technically be possible in the theoretical uh theoretically speaking however in practice it is far from uh it is it is quite far from reality i would say there are several limitations and several reasons why we may not b