Level Up Your Django Performance with Mohammad Ahtasham ul Hassan and Shafqat Farhan Ahmed

Carbon

Speaker 1: Good morning and uh good afternoon, everyone. Uh wherever you are and uh in whichever time zone you are in. Uh my name is Mohammed Shamul Hassan and uh today along with my co-presenter Shafkat Farhan Ahmed uh we'll be presenting uh to you the topic identifying and theming n plus one queries. Uh before diving into the topic uh a brief introduction about us that uh we both work as uh software engineers uh at Arbisoft which is a service uh software services company in Pakistan In this talk, we will explore how performance can be impacted by N plus one QE problem and

Speaker 1: more importantly how we can tackle it and resolve it effectively. Over to the agenda. First, we'll start with the overview of ORMs and how they interact with relational databases. Then we will discuss how performance is important for us in software applications. We'll also dive into uh what is the N plus one problem actually and how it impacts uh performance of our applications. We'll show you an example to help identify the N plus one problem uh as well as we will look into uh the strategies

Speaker 1: to explore uh to uh resolve and uh to uh you know uh fix uh these problems uh the problem of uh the n plus one curies and uh finally uh we will wrap up with some key considerations and uh takeaways uh before uh opening the floor for all of you for any questions that you might have. So Let's move forward to the overviews of ORM, which stands for the Object Relational Mappers or Mapping uh that provide us a layer of abstraction uh and simplify our uh database uh interactions uh by automatically generating uh queries for us. So they provide us uh an abstraction on the top of uh the database and uh help us uh execute the queries

Speaker 1: uh uh more easily and without uh actually writing the queries by ourselves. Uh and on the top of that Django ORM uh which we will be which we'll be using uh like many others it operates on the lazy uh curing principle which means that uh when we write uh any operations that deal with the uh database Uh they are not actually evaluated at that time uh but uh they are uh actually evaluated uh when we actually access the database. While this can be efficient, it also introduces the N plus one query problem , particularly when we are dealing with large datasets where it can be actually problematic. And this problem

Speaker 1: can significantly impact the performance of our software applications So next we'll look into why the performance is actually beneficial and important for us before diving into what is N plus one problem and how it affects us So why performance matters for us? First and foremost, it impacts our user experience because slow applications uh tend to frustrate the user and uh in result it uh results in poor user retention for our applications And performance issues can also lead us to timeouts on our applications and even random crashes

Speaker 1: due to the memory overloads. And uh one important uh aspect is the search engine uh optimization, uh, where search engine rankings will be affected uh if our application is slow and uh leading to uh low user turnouts and reduced visibility for us. Ultimately, what all these things contribute to is the low user turnout for our application And um uh this is the downside of uh low performance applications for us. So Uh while using ORMs, uh we have to consider all these impacts of uh performance. Uh while the abstraction that uh the

Speaker 1: ORM provides to us is beneficial for us in many cases, it can also cause headaches for us in many cases. other cases as well. So we have to be mindful of its impact on our applications, particularly when dealing with applications that deal with a large volume of data set. So uh let's move forward to the actual uh problem that we'll be dealing with. That is the n plus one problem. So uh uh the N plus one pro problem in itself is uh where we have uh uh single Curie for our actual uh database uh model and uh in turn uh

Speaker 1: it uh also makes n queries uh where n stands for the number of records which are related to our actual call. So this scenario can uh occur in cases where we have uh foreign key mappings, foreign key relationships actually, uh where we have um uh many-to-many relationships uh we where we are doing some reverse uh foreign key lookups and uh it can also occur for us uh where uh we are using templates uh that execute or m queries or uh we have DRF APIs uh Django Rest framework uh serializer APIs where the serializers rely on uh ORM queries that in turn execute n plus one queries Uh

Speaker 1: so uh if this issue is so widespread and so common, uh why doesn't the Django ORM uh itself handle this? It trickles down to two main factors which are performance and flexibility. As we mentioned, that ORMs use lazy loading to minimize the initial query size uh which is also beneficial for us with uh large data sets uh when we are not going to uh you know utilize or uh we when we are not going to need all of the related dataset But this flexibility comes at the cost of performance in certain scenarios with large data sets and where we have complex relationships. So as an example, uh

Speaker 1: we have uh taken two uh two models uh here uh the conference model Which is a simple model having uh two fields and the top model uh which contains a foreign key relationship uh to the conference table And next up, uh we have a use case where we'll try to fetch conference names uh from their respective talks. So first uh we uh fetch all the talks and then we try to fetch uh uh uh all their talks uh talks conferences so from the outset uh if you look at this code this uh just looks like a simple code uh which gets the job done for us But actually uh deep inside that uh lies our N plus one problem and uh

Speaker 1: for uh actually uh Explaining that to you and identifying that problem. I'll hand it over to Shafkat who will help you identify the problem as well as rectify and resolve the issue. So over to Shafkat

Speaker 2: Thank you, Adam. Uh before I jump into identifying the problem I want to highlight the importance and the impact, performance impact of the N plus one problem in a software application. First and foremost, it leads to increased database round trips for each query. For example, every time we evaluate the data, a new database query is executed to fetch that data. Eventually Increases the number of round trips made to the database. This, as Atisham has already explained, will result in a D

Speaker 2: in a degraded user experience. and uh end users may face low uh page load times or unresponsive pages. Another performance hit could be that our servers are working harder than necessary because uh leading to leading to the suboptimal resource consumptions because of the inefficient QD execution. And lastly with respect to Django Rest framework API's timeout might occur more frequently due to obviously as I explained earlier the suboptimal or the inefficient QD execution

Speaker 2: Okay, now let's dive into identifying the problem. I'll use the same example that the Sham showed earlier. uh with you Django models. Uh yeah. Thank you, Ethisham. Uh so this is the previous slide that Athic Sam explained. Uh in this there are two models conference and talk uh which have a foreign key uh relation Relationship between them. Now let's assume we have six rackets in the talk database table. I use the Django debug toolbar to evaluate the SQL queries, executed uh uh during the evaluation of the uh of the query set. As as we can see

Speaker 2: there are total seven queries executed One for retrieving the talks and six more, one for each talk to fetch the associated data, which is the conference models fields. So this in particular is an n plus one problem because total seven queries are executed and ideally we should only need one. Other ways of identifying this problem early in the stages is conducting uh detailed or thorough code reviews and writing unit tests. Unit tests are helpful because we can

Speaker 2: we have an ability in Django to excuse me to write the unit test to check the number of queries or the to assert the query count so we can uh tackle this issue in the early stages or before even uh deploying the changes to the production servers. And there are few other options we can use third party packages that that detect this problem and plus one problem The one that I in that I've used is the most commonly used one which is the Dango Debug Toolbars SQL window Okay, so now we'll explore the options that Django provides, Django Goarm provides

Speaker 2: to fix this problem. There are two methods that Django ORM provides to eagerly load the data where required. First one is select related. This is effective in models with the foreign key relation. It allows us to load the relational related data in a single query, reducing the number of database round trip just to one. for uh for many to many or reverse lookups or uh uh for one-to-many relations Django ORM provides us with prefetch related method. This method performs at least two queries if we have just one prefetch prefetch related method added to the ORM query.

Speaker 2: So there will be at least two queries and then the uh data is loaded into the memory and the joins occur in the Python code. Okay, so I've explained both of the methods that Django ORM provides now let's uh take a look Sorry, at our example problem and let's apply the fix. So for the certificate related uh as uh we've seen in the previous slides like uh the talk table The talk model was fetching all of its data and the related data was being evaluated and additional queries were being executed. So if you can see the first example under the select related section, we have added select related method to the QD.

Speaker 2: What it will do is So this method will eventually fetch the associated data in a single query. Next slide please. So here as you can see once we have applied the fix, the Django debugs toolbar SQL window shows that only one query was executed. And this query is now a bit more complex because it involves inner joint operation. But we have achieved our performance We have uh improved our performance by just uh curing the database or reducing the database round trips just one. For large datasets, this will bring a significant boost in the performance, especially when we are dealing with enormous

Speaker 2: datasets. Okay, so as we wrap up, here are some considerations and key takeaways. First, um the performance impact Of n plus one QRs will be minimal or small dataset, which is obvious asham has explained earlier. However, the as the data grows, so does the impact And uh solving the n plus one problem in large and complex datasets can sometimes cause ripple effects. Yes, they do. For example Pre-fetching large datasets

Speaker 2: can lead to increased memory usage. So it's important to be mindful of this trade-off. Because uh as we have explained earlier, if we have increased memory then we might or users may face server crashes or uh server performance might get a hit So uh third takeaway is that avoid we should avoid pre-fetching related data if there is no specific use case Mindful data fetching is crucial here to maintain the performance. This is why most ORMs work on a lazy loading principle. So it has its own benefits. And finally, we do have to be mindful here, like when working with Django Rest framework, serializers, mostly the model serializers

Speaker 2: also. use or work with Django ORM so fetching relational data can also lead to N plus one problems there as well Here are a few resources that are from the Django's official documentation, which you guys can take a look. Yeah, that's all for the presentation. If you have any questions, please let us know. Thank you