 Okay, so Leo, I'm a tech lead at Jiru, which is a FinTech in Brazil. It's a company that provides credit for people who need a quick loan. I'm not an expert, not a particular expert in SQL or SQL alchemy or Amazon in general but I've learned a few lessons optimizing some code in my company last year that I would like to pass along. So Jiru, as I said, is a FinTech in Brazil. Our stack is mostly Python, Pyramid, SQL alchemy, Postgres. There is some MongoDB, some Celery, some Java somewhere. We have been carving out this monolith that we started it with in different services and so some new services get written in some different languages, but most of the time we end up picking Python, Pyramid, SQL alchemy and Postgres. And I really like SQL alchemy. Well, it has two aspects. It's the core, which is basically a DSL for constructing SQL queries using Python constructs. And then on top of that you have the ORM which is, which helps you map tables to classes and records of those tables to instances of those classes. And for a programmer who mostly knows Python, it's obviously a lot more comfortable dealing with the instances of an ORM than it is juggling SQL and we at Jiru use the ORM most of the time and we tend to use very little the SQL alchemy core for writing SQL constructs directly. I think SQL alchemy is awesome. However, whenever you choose a framework, it's usually good if you know what the framework is doing behind your back because frameworks still require you to make decisions about how to use them and knowing the underlying patterns is essential if you're to make wise choices. So using the ORM is really comfortable. As I said, you declare your class, you get instances from it, and then you're manipulating it with the attributes and the methods like there is no SQL database behind it, and that's really comfortable. But it's also a problem because the database is an external system. Most of the time you should actually treat it as if it were an API of a foreign system. Because it is, really. It is an external system you're talking through through a TCP IP connection or a UNIX socket connection. And the API to that just happens to be SQL. So what happens then is that you're writing your Python code, and it looks like perfectly normal Python code, but ends up doing bad performance access to your database. It's noticeable in low volumes, like when you're developing or when you're just going to production. So you get glued into this false sense of security that everything is going fine. But then after a while your database starts to crawl, your application takes too long. The fix for that, of course, is to let the database do its job. You need to be aware of the implicit queries that your ORM is doing whenever you access your instances in your classes. Especially when you have relationships between your instances that map to different tables in your database. Those are the ones that tend to to cause the the most bad pattern of accesses. You should try to do only approximately only one or a constant number of queries to your database per HTTP request or API request or background job. And you should avoid as much as possible looping through instances of your model in Python code. Because the database is a lot better at doing that. You should also be mindful of the amount of round trips you do. You should try to try to do only a fixed number of queries per request because every round trip costs time. But you also should be mindful of the amount of data you're pulling out of the DB when you do some requests. So I'm going to talk about specific cases here that I've optimized. We had this report that we ran one that we still run about once a month. And in the beginning it was really fast. As years passed it was taking over 24 hours to run. So let's talk a little bit about Jiru. I don't know if it's readable. But Jiru is a credit company. It provides loans for people who access our website. We do everything online. You just snap your documents and then we after you're happy and do a credit analysis we send money to your bank. And then you pay back your loan by paying bank slips and you never have to actually face. You can actually you we are designed so that you don't actually can you cannot actually visit us. To talk about your case is everything is done online. An early funding model for us to get money to lend to people was that Jiru created this funding company, a separate funding company, that issued debentures. The venture is like an official loan from a company that the market can buy to give money to the company without becoming a partner without it being a stock option. So the company would issue new debentures every six months and these debentures had a contract that said that whatever the loans the borrowers would pay back would be the payback for the the people who purchased the debentures. So the company was never insolvent. Of course, we still want to have a good credit model. Otherwise people will not want to buy our debentures. So we issue the debentures every six months and the venture holders buy these debentures. They put money in the company and so we grant loans without money and the borrowers pay back those loans and at the beginning of every month we'd look at what was what we got from and payback once a month the debenture holders. Of course, it's a lot more complicated than that because part of the money is the amortization, which is the money that the venture holder actually lent us and then on top of that you have the premium, which is what you are paying on top of what the venture holder put in so that they can be happy with their investment and taxes affect only the premium, not the amortization and taxes. You pay less taxes, the longer it takes you to pay amortization. So we do dances with the numbers like we are only paying amortization for a while instead of being premium. Then we save a little bit because in Brazil you cannot only pay premium. So we have to save some of the amortization and then start paying back the premium and then paying the amortization last. So there's a whole bunch of numbers, but that only happened in later the venture issuances. The first the venture issuances we were paying back from the principle of the loans that we granted to the amortization and from the and from the premium of the loans that granted the premium of the venture holders and the entity relationship model looks somewhat like this. You have you have the the venture holder, the venture belongs to the venture holder and to the venture series. The venture series has an account. All the operations from borrowing and the payout come from this account. So we have a bunch of operations and if that sounds awfully complicated, I don't know where it gets a lot worse. So let's look a little bit at the code. How do we actually code that? So first you have this base class from SQL Alchemy. We declare it by putting some creating our own base class with which you called here or M class and we added a convenient class method here. It's actually class property. Yeah, so what we do here, we take the SQL Alchemy DB session and make it easily available inside the class so that you can say, oh, I have my model dot query and then you can apply filters, order by, joins with other classes and things like that. So it's a little convenience attribute in the class. And so this base here, there's this method, this function from SQL Alchemy that makes this the reps or M class and you have this magical base class here, which we then have to use in all the models that we declare. So, like we said, the debenture, which is the thing that we sell to the debenture holders, which is like the loan that the debenture holder is granting to us. It has its database key. It has a serial number which is very different from the ID because the serial number repeats inside every debenture series. Whereas the ID is constantly different for all the debentures no matter what. There's a sale price because if you buy at the venture at the beginning of the series, you buy it close to the official debenture price. But if you buy it later, it costs you more because it's the price of opportunity for being late to the party. And so we have a sale date. Here we are continuing the same class. So those here were regular attributes of the class which get mapped to columns in the database in a table called the debenture. Here we are talking about the same class, but here we see some relationships. So we have this holder ID, which is also a regular column, a regular integer column, but then we add a declaration that it's a foreign key into the ID column of the debenture holder table. And on top of that we declare the holder relationship, which has as foreign keys the holder ID that we just declared. So this way I get a holder attribute on my debenture instance, which fetches me the debenture holder instance transparently for me, but this transparently means it's doing a query to the database, at least once. During the session, SkaLock me actually will cache that instance so that so that it doesn't fetch it again every time I access the attribute. But still I need to be mindful. The first time I access this holder attribute, there's going to be an SQL query, unless I play some tricks on it. So as I showed in the graph, the debenture also has a relationship to the series, to the debenture series that is issuing this debenture. And again, we have a series relationship mapping to these column. There's another interesting aspect here, which is this background. We saw it here at the holder declaration as well. What this does is create debenture's attribute in the debenture holder class that points to an iterable kind of a list of debentures that I can conveniently access from the point of view of the debenture holder. Same thing here in the debenture series. I get a debenture's attribute there, which is an iterable of the debentures that points to that debenture series. There's this lazy dynamic here. What does it mean? That actually tells you what kind of iterable it is. If you don't say anything, it will create lazy attributes that the first time you access it will make a query to the database and bring back a Python list. But if you do like I did here and say dynamic, then instead the debenture's attribute inside the debenture series will be a query object, pretty much like this query of this query object here that allows me to apply filter, order, buy, and other things like that. So by doing these dynamic relationships, I enable the application to lazily get a query object, apply another other operations on top of it, like filtering, ordering, joining with other relationships, and only then when I try to iterate over it, it goes to the database and fetches the data. So I had to debug an issue that was found by the financial people that we are not paying exactly what we should be paying the debenture holders. There was some discrepancy and I started debugging this hours-long report. It took hours because well, the report itself took about five hours, but it also depended on another process that cashed some information, and this other process also took about four hours, and that was for each debenture series that we had, and by the point we had about six debenture series, so the whole run of reports took more than a day. And I was starting debugging that and saying, well, this is taking too much time, so I enabled SQL Alchemy to do debugging for me. If you take the standard Python logging, you take the SQL Alchemy and GIN logger and set the log level to info, it will log every query. If you set it to debug, it will log every query and the results, and when it logs the queries, it logs the parameters that are used by the query as well. So I enabled the logging and started running the report, and suddenly I'm seeing gobs and gobs of the same query repeated over and over and over. So what I did, let me find the diff here, so let's see some code. That's not readable, right? The people in the back, can you read it? Nope. How about now? Nope. The people in the back can read it? Okay, I should be able to read it, right? Okay, so let's see if I can show the things. I think I'm going to switch to mirror mode. So so this is going to be kind of hard to fit all the code in this space, but the kind of things that were being done here, so we have this total pading. What does this method here do? It gets all the money that was paid in the ventures of that debenture series. This is a method of the debenture series class, and so you can call it by specifying a period if you want, and it gets the debentures that are owned by someone, the debentures that are actually sold, and this is a property that brings the debentures related to this debenture series that actually have owners, and it's adding filters here, if you pass the start date or end date, and then it's doing some of the sale price of all these debentures. The the moment where the code actually goes to the database to fetch is the moment where Itter is called into this object, and what this is doing here is taking a huge number of debentures, pulling all their columns from the database, just to sum their price. Now, when you look at this, it's perfectly reasonable Python code, right? You're summing the sale price of a bunch of debentures. That's exactly what you want, but here you are pulling a huge amount of information from the database just to get the sum of what is essentially a column, and if we had done something, if instead of sale price, we had done something like D dot series, I cannot do that, D dot series dot sale price, then not only would I be fetching a bunch of queries, a bunch of information with all its columns, for each point in the loop, I would be fetching another record in the database with all its columns and then summing it. This is called the n plus 1 select problem. So instead of doing that, what I did here, I created this function called get column sum for query. What's the query? The query is debenture, so you see it's the same word here and here. But then I'm going into the class instead of the instance to get the sale price column. So I did not actually have to write SQL by hand to have SQL Alchemy do a performance query for me. What does this get column sum for query here do? It's right here. First, it gets the query. It assumes the iterable is a query. It drops whatever ordering it has, I'll explain later why, and replaces all that it was going to fetch with a single expression, which is this coalesce sum of the column. What is coalesce sum? It's right here. It's using SQL Alchemy func sum, which is a representation of the sum function of the database around the column. Now, this doesn't need to be an actual column. This could be a column in our expression or anything that feels like a column to SQL Alchemy. So it's using the sum aggregating function from the database. And then it's calling coalesce with zero to it. Why is it doing that? Because in SQL, if you do a sum of a bunch of records, of a column of a bunch of records, but this sequence of records has no records in it, instead of getting a zero, you get a null. Or if you have records, but all the columns in those records are null, you're gonna get a null back. But most cases, when I want to use a sum, I actually want a zero back in those cases. So I create this coalesce sum function to return a sum of a column or a zero if there is null in there. Please ignore the filters and label for a while. We're going to go back to it later. So I replace all the entities in the query with just the coalesce sum of a specific column. And if you remember it, it was the banger's, let's say, a price. And now the reason why I drop whatever ordering the query had is because since I'm calling an aggregating function, either I have to have a group by clause on the SQL or whatever ordering I have, whatever ordering I have needs to be part of this group by, or I cannot have an order. So I make sure there is no order in this query, especially since I'm not using a group by here, in this case, and which means that the database will return a single record, and in this case, a single record with a single column, which is the sum I'm asking. Because of that, I'm calling the scholar method from SQL alchemy, which does exactly that. It gives me the number that is in this, the value, not necessarily a number, but the value that is in the single column of the single record of the query that I just did. If the query is not a single record with a single column, it raises an error. But this is a very convenient function when I want just a number, which is a sum of a column. So coming back here, instead of looping through a huge number of objects, with all their columns just to get the sale price, I'm asking the database to bring me exactly the sum that I want. And I got that in the diff with a single line that's very readable. So in this calculate total paid out, which is everything that I have already paid to the debenture holders, they have the same issue. But here, instead, they were looking at the payouts, which were the operations of payment operations. And then I replaced by the same thing. Going back a little, going under a little bit more, there was this whole method here. It was called calculate total values involved, which was doing a huge amount of those things, getting the sum of a bunch of operations. These was taking a very long time. And the operations were all a result of calling these methods here. Payback operations, earning operations, earning tax operations, and things like that. So when I looked at those methods, let's search for one of these methods here, for example, earning operations. So what it was doing, it was looking at self.operations, which is one of those query relationships that I showed, filtering by a specific tag, filtering it by some specific dates, and returning them. And all these operations, roofing operations, earning operations, earning taxes operations, were doing the same thing. So what I did first was to factor out the data filters and create this class that's really just a record that's collecting filters. So a payback filter is this criteria, source type equals note, tags equal note, payback. Agirufi is this filter, Ernest is these filters, etc, etc. And what I did with those was I refactored those other methods to get those operations, call these operations in period, which is where I figured out the the data queries, the data filtering of the operations, and replace those with the filters applied. Why is that useful? Because then I could also do this other method here called calculate operation summary, which returns a single record. What record is that? It takes all those operations the same period, order by none, replace all the entities with the summary columns. Why I call them summary? Because just like a bisterior is a collection of bists, a summary is a collection of sums, and that's exactly what this method does. It takes a coalesce sum of the same columns that we were looking at, giving them a labels that are like the variables that were being collected in that method above, and returning those as the columns that I'm going to put in the query. And because this is a coalesce sum and I'm not doing a group by, the result of this query is going to be a single record, which is why I call the one method that returns that single record. So when I use this method here, I get back this operation summary, which is a record that has as attributes the labels that I pass to the columns. So when we get here, that's why we have this label parameter here in the coalesce sum, so that I could give that specific label to my summing column. And what is this filter here? There's this characteristic in SQL where if you have an aggregation function in a column like sum or average, you don't necessarily need to do that over the whole of the records, or the whole of the records of a group. You can actually apply for the filtering. What does that look like? So that isn't readable, right? Is it readable now? Okay, one more, just to be sure. So yeah, this is nice to show as well. I have some of those models that I declared. I created the minter holder, added it to the database, flushed it so that it has its primary key. And here I show what a query for the banchard looks like. In SQL alchemy, you can print a query, and it gives you the stringified version with the parameters there in place. So I... But it's nice to know what the parameter is going to be. So I created this function that formats and colorizes output and tells me what are the parameters. So if I do the banchard query, but filtered by, created the bigger ton today and holder equals a certain holder, that variable that I created just above, it generates this query to the database. So we can see that it has created here and the debenture holder. And I can see what parameter is going to pass. So created is a date time with this value, and the parameter one here, which is the debenture holder, has this integer value here, which is the primary key of the debenture holder that was doing it as the filter. Now, the nice thing about here is that I'm actually comparing the relationship object, not the ID of the object here, but it translates me into comparing the ID in the query. So here I created the debenture added to a series. The debenture series object is actually complicated for relationships, so I created a factory for it. And here I can check that the holder ID, the debenture holder, is exactly the ID of the debenture holder that I created. So when I look at the debentures of the debenture holder, just like I said, it's a query that selects all the debentures that match that debenture holder. If I also filter by the debenture state and sale date, then I get this other query here with all these parameters replaced. So going back to our optimization, the debenture series operations is this query over operations here. And when I ask for Jirufi operations, which was that method, it's the same query over operations, but with added filters in the where clause, right? That means, okay. So here is what I did with the operation summary. I replaced all the columns in the operations with those columns. And since the formatter wasn't very good, I did my own formatting here. Let me show that instead. So instead of, since I added those filters at the column level, instead of putting those filters at the where clause, it's actually putting those filters along with the sum. This is very useful for things like, I want to know the percentage of clients that have a certain characteristic over all of the clients. I can do that by doing a sum filter and dividing that by the sum without the filter. And the database fetches that for me. I don't need to do two queries, one with the where clause and one without, and then dividing it at the Python side. I can have the database do that for me at the database side. So here we have the operation summary. It creates a bunch of columns which have these filters and doing the respective sums for me. So here we have payback operations. And here we have, so you have these tags here and these tags there are here. So we have note paybacks and fees and earnings and things like that. So it's filtering them at the select clause. In the where clause, it's just making sure it's selecting the operations with the right state belonging to the right account and in the right time range that I asked. So with a single hit to the database, I selected subsections of all the operations, did a bunch of calculations for them and got exactly the information that I wanted. And with these kinds of things, I got completely rid of the cache and brought down the time of the report from nine hours per series down to four hours per series. Why four hours? Because the rest of the time, it was not just a report, it was actually inserting the debenture payments into the database because next time I wanted to run this report, I wanted to run a difference and anything that was not collected correctly or any rounding error should be paid in the next month. So to optimize the insertion, I had this, I had the system, let me locate it here. So the report already had some optimization done before, which was to calculate the insert query for each debenture payout manually. But it was still calculating one insert per debenture every month. Of course, most of these inserts were, they look exactly the same because all the debentures bought by the debenture holder on a certain date have the same calculations. So they were caching the calculations by sale date and holder ID, but still they were looping through all the debentures. And then creating those inserts one by one. Instead of that, what I did was to loop through only the, each integralization. An integralization is a set of debentures bought by a holder in a specific day. So I created this criteria, which is the integralization columns, which is the holder ID and sale date. I'm adding a count of the debenture so that I can do the proper calculations. I'm joining the debenture holders and the series here so that it fetches everything at the same time. And I'm asking for a distinct query so that the database only fetches one debenture per integralization. And because of the distinct, I need this ordered by the integralization columns and the debenture series number. What does the query look like? So because I asked for a distinct, Eskel Ochme rendered this as this distinct on query here, and then selected all the information from the debentures but only of one debenture per integralization. And then it joined that information, it's kind of hard to read here, but it joined that information with the similar information I needed, like the sum of the debentures paid and things like that. So it goes with a single hit to the database fetches all the information and then I can loop and it's all calculated. But the most important part here is that instead of calculating the inserts by hand, I get the debenture payout table and ask for an insert query. And then instead of a regular insert, I do an insert from select. And I select from the integralization debentures, replacing the entities with the columns that I need to populate one debenture payout. What does that look like? So here I do the same query and what it does is it does an insert into the debenture payout, all the columns that I selected, and what it's inserting is a select of all those parameters. Some parameters are constant, some other parameters aren't from the select. And then it's mapping all those information. So instead of doing inserts one by one, manually reading SQL from the Python side, I'm actually asking the DB to do the inserts for me. And that brought down the report time down to 15 minutes from nine hours per debenture series. The last optimization I did, and this is the one that justifies from minutes to milliseconds, is I optimize a page rendering based on how much time it was wasting authorizing the user. Let me find it here. So during the authorization phase, while rendering a specific page, it was checking if the user had permission to see certain bits of information. This is server side rendering, so it's not so fun to do these days. But it was doing user.admin.has permission, except that admin is actually a property that does a query to the database. So it was doing a query to the database every time. And then asking if, and getting an ORM record and asking if it has permission, which also goes to the database to fetch if that permission belongs to the user, it was doing that all the time. So just looking at this code, I already said, well, just pull that has permission out. But the biggest part of the optimization actually happened, well, I replaced the property by Reify, which caches the property and fetches only once. But also, instead of having those profiles be dynamic queries, I did the opposite. I pulled all profiles at once also throughout a scale of my relationship. So we have this all profiles relationship here that is not a dynamic query. It goes to the database once when it is accessed and pulls a list. And so we have a Python list every time you access it. And then I created a get matching profiles, which is a Python file that loops through this list. Now, the thing is, this is a tiny list. The roles of our user is a tiny list. So it actually makes sense here to pull the calculation out of the database into the Python side, because I'm minimizing the amount of data that I'm circulating. And the other big part here is that while fetching the user for the authentication, well, fetching the user for the authentication, I'm actually instructing SQL Alchemy to join the load all the profiles and join load of those profiles all the permissions. So it does a single query pulling the user all its profiles and all their permissions in a single hit to the database. And now when I access the properties of this linked data structure, I'm not going to the database anymore. And that brought down the pages that took minutes to render down to milliseconds. Now, so in conclusion, and yes, there is a conclusion. Okay. Yes. To figure out what was happening, I used this, there's this Python package called slow log, which is good for WSGI applications. When a request is taking too long, it starts dumping stack traces into a log file. It's perfect to see what is wasting time where without you having to be there when things go slow. You can just go to log file later and retrieve it. And then I saw that that function there with the admin was the slowest one. So in conclusion, ORMs are very nice to get started if you're a programmer and not very familiar with SQL. It's a good way to get started. I'm not dissing on ORMs here and I love using ORMs, but you should understand your SQL. Read the select documentation of your database and try to understand what every bit does there. I figured out that you could apply filter where inside some columns by reading the select documentation. Understand group by and aggregations and how aggregations reduce the cardinality of your functions. Learn about aggregation functions with filters. Learn about distinct on and window expressions that will help you write SQL that fetches things very efficiently. And then, be aware of the underlying queries that it does, push as much work as possible to the DB, but not too much because sometimes your career is going to spend hours. And that's it. Thank you very much. But I'll be around if you have any. Just come talk to me.