Choose, and choose quickly: Optimising ModelChoiceField - Carlton Gibson

Speaker 1: Hey, thanks for joining me. Welcome to my home. It's funny, funny experience, funny situation. Normally I'd be looking out at the stage at you and I'd see you all and uh be able to see your smiling faces but I can't so I can just see my slides. That's okay though because I've got a row of teddies lined up. So I've they're they're my audience, they're you or you're them, whichever, I don't mind which. Anyway, thanks for joining me. This is me. I'm Carlton Gibson. Carlton Gibson on GitHub and Twitter. You can find me there. Nothing at all to do with this talk, but I do a podcast on Django with my co-host Will Vincent called Django Chat. Essentially, we have guests from around the community and we chat about Django. If you haven't already, you should check out the podcast, DjangoChat. com. This talk is called Choose and Choose Quickly. It's about optimizing model choice field.

Speaker 1: It's a topic that I think of as one of the first ports of call of everyday Django optimization. I see a lot of issue reports. I'm lucky enough, along with my colleague Marius Fliciak, to be a Django fellow The Django Fellows Maris and I are contracted by the DSF, the Django Software Foundation to do the day-to-day maintenance that keeps the project going We handle things like treat uh um ticket triage, we do patch review, we do security uh issues, we handle the releases, right? It's the kind of stuff that on a project the size of Django's just otherwise wouldn't get done And as a fellow, I see a lot of tickets. We get three to five new tickets a day, every single day. And then when I'm not being a fellow, I help maintain various packages in the Django ecosystem. And for this talk, the relevant the relevant ones are Django Rest framework

Speaker 1: and Django filter. And they have a lot of tickets too. On all three, I see frequent issues that come on, come in, and there's some variation on Model choice field is slow. Django slow or the admin slow or form rendering slow or the browsable API slow or Django filter is slow. And when I say day-to-day, I mean it. Literally. Just while I was preparing this talk, there was a thread on Twitter. Enabling the browser API took rendering from one second to 20 seconds. How do I disable that? Can I do the same for Django filter? Right. Another issue on DRF, Django Arrest Framework, again, whilst I was preparing this talk, a neighboring filtering on foreign keys slowed down rendering by 10 times. Another issue on Django filter, again while preparing this talk, form

Speaker 1: rendering is making too many database queries. The point is that I see a lot of these. They're really common and they affect everyone. It's not quote unquote a beginner issue So that's what we're going to look at. We're going to set up a simple Django project and then we're going to show three examples of how how slow performance with model choice field can arise. Finally, we'll see three ship strategies for addressing that slow performance. My hope is that the next time you're working with a model choice field, maybe in the admin or with DRF or in Django filter, you'll know how to make it behave. So let's go. Let's start with a simple Django project. We're going to have a couple of models. First of all, an author. It's just got a name field, nothing else. Then obviously we've got an author, we need books. A book field has just a name again and then it has a foreign key to the author.

Speaker 1: Let's set that up to use with REST Frame Framework. We'll have a couple of REST framework serializers. So we have an author serializer which just serializes the name and then we'll have a book serializer which serializes the the name of the book but also the foreign key to the author. Then we need a couple of views. We'll have a book list which is a list create API view, which is one of the generic views that REST framework provides. And we'll just give that the query set of all the book objects and we'll give it the book serializer to use And then we'll have an author detail view where we can drill through to that. And um we'll give that all the authors and we'll use the author series Serializer. If we root all that up and give it give it the the Django Rest framework will give us the browsable API that we're perhaps used to, you must I'm hoping you've seen this.

Speaker 1: We get a nice list of books. shows all of them. It gives a hyperlink there to the author detailed view. And down at the bottom we get a nice generated form to create a new book with a drop-down to pick the author. And this drop-down is our model choice field. Where there's a foreign key, it will enable you to choose which which object is the one you want to uh populate the foreign key with. Now in this one it's Res Frameworks. It's not strictly model choice field. It's model framework it's Res Frameworks version of that. Because REST framework uses serializers, not forms, but for our purposes it's the same. The important point here is that there's two SQL Nice and quick is 100 milliseconds to render or something like that.

Speaker 1: For reference, here's the author detailed view, and that's got one SQL query. So What's the problem? Well, there are two. The problem with this with model choice field is that if you give it lots of data, I've only got 10 authors, but most production data sets have a few more than 10 rows. If you get give it lots of data, it can slow down. The second part of that is Is problem two when it does lazy related object lookups. That's where you've got the book in hand. So you've already fetched the book, but then you want the author. And so the Django RRM has to go back to the database and request the author object for you. So we're going to see that in action. And here for these examples, I want to thank Andy Id for a lovely data set which is of Australian suburbs. So let's look at those. First of all, we have states, Australian states. We have a state model, Queensland, New South Wales, and so on.

Speaker 1: We've got a name, which is like QLD or NSW, a long name, which is the full name Queensland, and then a couple of other properties. Then we have a suburb. The suburb is just a name and a postcode, and then a foreign key to the state. Now the first thing, the first thing here is that there are 17,000 of these suburbs. That's a lot. And then the second is that in the string representation here, we've got this self self self. state reference. Because Every state's got a red hill. So you need so you need red hill New South Wales, otherwise you're going to get lost. Now this is going to get let us see the lazy related object lookup in action, right? Now the the example itself might be a bit contrived, but the point is you can't get rid of these relationships, these foreign key relationships in your models.

Speaker 1: So let's put these models in place and we'll see we'll see three examples of the issue in action The three examples. First we're going to look at a Django Rest framework serializer rendering its form. Then we're going to look at a regular Django form using Django filter. And then we're going to look at the Django admin with list editable. So let's get straight on. Example one, Django Res framework serializer form. Let's extend our author model. Okay, so we give it a new relation to the suburb, which is this foreign key that we've this is the new model we've created Then, if we update our serializer, and all we have to do is add in the second field here, the new suburb field, and we can refresh We get our author detail view and we get with the extra suburb field rendered and we get the form there at the bottom.

Speaker 1: And if we click on that form, we get our drop-down with our foreign key options. But We've got 1,022 SQL queries. We went from one query on this view. Now why the cutoff? Why only a thousand? There were meant to be 17,000 suburbs. Well, Django Rest framework. knows that you don't want to render 17,000. It has this cutoff. You can set that to whatever you want, but by default it's a thousand. So we did all that work. We did that thousand SQL queries and we still didn't get the full full choiceless rendered. We'll come back to that. But regardless, a thousand queries is not good. So the A the Browser pull API is slow, goes the complaint. Example two is a regular Django form using Django filter. So let's go to our book list.

Speaker 1: What we have to do here to use to use filtering. Is to add the filter back end argument to the view list. So we give it the Django filter backend and we specify the filter set fields that we want to filter on. So let's filter on author and the author 's suburb. Then when we reload, we get our same nice book list. And this is that but there's this cute little filters button up at the top. If we click on that, we get our drop-down with the suburbs. And it's all of this time. All of them this time, all 17,000. Our brisant browser might lag a bit displaying that full list, but it's there. However There were 17,871 SQL queries. Ouch, right? Django Filter uses a regular Django form, which doesn't have the cutoff that DRF puts in.

Speaker 1: So it just keeps on fetching Now if you happen to have say debug toolbars SQL tracing on when you run this this kind of query, it will take an age. That's one reason to keep debug toolbar SQL tracing on in development because you're going to notice it. Likely you don't want it on in your tests though But regardless, 17,000 queries is way too many, so Django filter is slow. Onward. Example three, the Django admin with list editable. So this time let's extend our book model. Here we're going to add foreign keys to simple publisher and topic models. They just have a name field, they're nothing to them. Okay. Then with our with our book model extended, we're going to add an admin. So we put list display, we want the ID, the name, the author, the publisher, and the topic. Brilliant. Let's load it up. And there's our nice admin.

Speaker 1: And look, it's Django 3. 1, so it's got a nice sidebar, all looking good. And then we're browsing in the admin docs. We're trying to remember one of the billion admin API options. And then we see this list editable. What could possibly go wrong? Well let's add that then to our admin. We could put l both publisher and topic, the foreign key fields, and we reload our Amin and we get this nice list view with a nice form and it's got options for each of the foreign keys. So the ingrid, the no-starch, the symphony, the starlet, the Django, the Flask there Here we had 35 queries. Now that may not look like a not a lot, certainly not compared to 17,871 queries, it's not. But let's go back to the screenshot Like for each row here, for each of the foreign keys, the the the the choice field is doing a lookup.

Speaker 1: I've only got 10 rows here, but the admin by default will show you 100 rows a page. And then for each of the foreign keys, I've only got 10 records, but you might have hundreds of records, you might have thousands of records, and then that soon starts adding up. If then on top of that, the string representation that goes into the select box there, the ingrid, the no-starch, the symphony, the Django, if that involves a lazy reference to a related object like it does with our suburb model , Then you're going to be in trouble. I was going to put a foreign key in here to the suburbs showing you how slow it goes, but it was just too slow. It took a couple of minutes or so without SQL tracing on. With it, with SQL tracing on. It for some value have never finished. It literally never finished. I put it on, go for dinner, come back, still not done. The point is that people enable list editable against a production data set and then the admin

Speaker 1: effectively freezes. Well, that's not good enough. So the admin is slow. So those were three examples, right? The rest framework form, the regular form with Django filter, and the admin with list editable. They show up, they show the main ways I see the problems with model choice field coming up time and time and time again. You do something simple, it works fine with a small amount of data in development. Then you put it into production with a decent amount of data and boom. Suddenly it's slow. So what can you do? Well, for three problems we've got three strategies. Let's look at do Strategy one, do less. Strategy two, don't repeat work at strategy three, do the work early. Let's look at those. Strategy one, do less. The issue is all these SQL queries. So each time we're trying to generate a choice for a suburb, we have to go off and fetch the related state.

Speaker 1: We do that 17,000 times. Well, there are only nine states, and that's essentially 2,000 identical SQL queries per state. The thought is, well, if only we could cut that down. Well, we can. Django comes with two great tools to help us here. Select related, which is more or less for foreign keys, and prefecture related, which is for many to many. Both of these allow you to say, hey, I'm gonna want that later. Please go and get it for me now so that we don't have to go back to the database and f next time I'm not going to focus on the details of these. There's lots of good stuff out there about select related and prefetch related, not least in the Django docs query set reference that goes into some detail. Instead, I want to show you how we can use these in our forms. Here I'm just going to use Select Related. So let's return to our REST Framework Author Detail view. All we have to do is update the serializer.

Speaker 1: Instead of letting the serializer auto-generate the suburb field, we declare it manually. So suburb is equal to a primary query related field, and then we give it the query set. So we give it subject Suburb. objects. all, that's the default query set for the suburb model. And then we say select related state, right? Please go and get the related states when you get these objects so that we don't have to go back to the database later Then we refresh. We still get the author details view rendered exactly the same. It's got the extra suburb field. It's still got the form at the bottom. If we click on the form, we still get our drop-down with our foreign key options. But We're back down to four SQL queries. That was from 1,002, remember. We still have to decide what we're going to do about that cutoff of DRF of Django Rest Frameworks. with 17,000

Speaker 1: records, that's probably too many to put in a drop-down, so we're going to need to do something. But at least now we're not knocked out of the game just by doing the default. At least now it's still speedy. Let's do the same for filtering. Let's look at the same there. DjangoBress framework has a generic filter backends that detach the filtering logic from the view. So generic API view, which is the superclass of all your views, your list API view, your create retrieve API view, they're all subclasses of generic API view. And that defines this filter query set method What filter query set does is it goes through a list of filter backends that are defined on the view, it instantiates each one, and then it passes it this, it calls its filter query set method to actually filter the query set. So in order to use this, we have to subclass the Django filter backend. So custom filter backend, subclass the Django filter backend.

Speaker 1: And we implement this get to filter set method. First of all, we call super to get the filter set. Now what is a filter set? A Django filter filter set does two things. First of all, it creates a form to pull the parameters you want to filter on from the query string. And then it uses those those it uses the the clean data from that form to apply the filter calls to the query tip. So we get the filter set and then we get its form and then we get its author suburb field, which is the one we're interested in, and we set the query set on that. to the suburb. objects. all dot select related state, which is exactly the same just saw the author detail view. It's this all the suburbs, but please go and prefetch or pre-op select the related states so we don't have to go back from later. Next we update our view

Speaker 1: There we just set the uh filter backend to be our custom filter backend rather than the default one, the default angle filter backend. And then that's it. The filter set fields are exactly the same. We refresh. Again, we still get our book list. We still get our nice little filters button there. If we click that, we still get our drop-down of options, all 17,000 of them. Again, the browser might feel a bit laggy here, but rendering those, but we only had six SQL queries. Now that's much more like it. Because we used Select We Related, we were able to render and even far too much data. We were able to render that data quickly. So that's strategy one, do less. It's probably the most important. The trick is that you tell model choice field exactly what to go and fetch.

Speaker 1: Shredd D2s don't repeat work. And here we're going to look at the admin example. Remember that we had And each for each row we had a query make for each foreign key. We were going to for each of the 10 rows, there's three foreign keys, and we had basically 30 queries fetching each of the data. What we want to do is reuse the same query set, one one per foreign key, but we could so that it's only fetched once, but we can't quite do that because model choice field does everything it can to install that assure that query sets aren't stale. So if you pass it a query set, the first thing it does is clone it. And then the next time you try and access it, it'll clone it again. So it's fresh. The only thing that's worse than slow data is wrong data. So instead of setting the query set, we have to set the choices directly on the form field. So in order to do this, we're going to create a form set.

Speaker 1: Now a form set is responsible for managing a list of forms. The table that the admin presents to us, that's a form set with one form for each row. So here in the init method, we get we set some properties on the form set. We sell self. author choices and then we instantiate a model choice field and we give it all the author objects and then we call choices and then we we're casting that to a list. So, because choices gives us an iterator and we want to reuse it, so we have to turn it into a list. So we get the author choices, then we get the publisher choices, then we get the topic choices. Then we need to implement a method called get form keyword args, which is how a form set can communicate with its form. For each form that instantiates, it's going to pass the keyword args that we that we return here. So in the key, we get this.

Speaker 1: the the super we call the super method to get the base keyword odds and then we add our choices in author choices publisher choices topic choices there Then with the form set in place, we create a form. This looks for the keyword args that if they're set, so author choices, if if all of the choices are in there, we'll have them. Published choices are in there, we'll have them. If topic choices are in that, then it calls the super method to instantiate the form. And then once the form's set up, if the true if the keyword argues were provided, we set them on the relevant fields Note, we're not setting the query set. We're setting the actual calculated choices. So when the field is rendered, they don't have to be generated again Then we go back to our admin and we have to update our admin to and tell it to use it.

Speaker 1: So there's two methods. First, get changelist form, which tells it which if we provide the form keyword arg there, it will use our custom form. Then we have get changeless form set, which we have to do exactly the same thing and tell it to use our custom form set. Then with that in place, we can refresh And we still get our form set with the drop-downs and all the rest of it. But we did with there were only 11 SQL queries, and that's down from 35, remember. So instead of doing the work each time for each foreign key, for each row, we do it once at the form set level and then pass that data down into the form. That's strategy two. Don't repeat work. Strategy three, do the work early. Well, our suburbs are totally static. They don't change. Maybe you know, they might change.

Speaker 1: Once in a decade. I don't know. We don't have to fetch them on every single request. Instead, we could calculate the choices up front, like we have at the top here. So the author choices, we could just put those in straight into a variable. Publisher choices, topic choices. Oh, we've got suburb choices we're calculating as well. This is the same code that we had in our form set at the moment ago, but we've we've extracted it. And then having done that, we can put those values into a cache. Now I'm just using file cache here and the none value says never expire it. I think I can manually I can manually expire if this ever changes. I I put this code in a cache choices script that I ran from the iPython from iPython with from the shell. But we If we do this, then in our form set, we can update that to use the cache values instead of generating the query sets each time the form set's instantiated.

Speaker 1: So here in init, we fetch from the cache values rather than calculating the choice. Everything else is exactly the same. Again, we refresh. Again, everything is exactly the same, but now we have Five SQL queries. Which is okay. Now here it didn't make too much difference in terms of speed. SQL Lite is pretty quick and file based cache is file-based cache which I used is pretty slow. But if your choices aren't changing quickly and they're expensive to calculate, then throwing them in the catch could be a really good way to speed everything up. So that's strategy three. Do the work early. So let's sum up. Apparently model choice field is slow. Well, we saw three examples of how it's slow.

Speaker 1: We used a desk Django Rest framework serializer and saw how that rendered. We saw a regular we a regular Django form with Django filter and how that rendered. And we saw the Django admin with list editable For those three problems, we saw three strategies, how you can deal with that. Do less work, that's the most important one. Don't repeat work and do the work early. The take-home message here is that really that model choice field is not slow at all. But you do have to be aware of the work it's doing and you do have to give it a chance to be performant. That's it. As I said at the beginning, I think of that this is one of the first ports of call in everyday Django optimization. You need to be able to set the query set on a model choice field, or if necessary, the choices on a model choice field. Thanks. I'm Carlton Gibson. I'm your friendly Django Fellow. I'm at Carlton Gibson on Gibb GitHub and on Twitter.

Speaker 1: You can find me there. If you haven't listened already, do check out the podcast at DjangoJack. com I hope you enjoyed the talk. I hope the next time you're using a model choice field, you'll teach it who's boss. If you've got any questions, do let me go. Thanks for joining me. Bye-bye.

Speaker 2: Recording is on.

Speaker 3: Uh yeah. Um so that's uh that's uh adding select related preparation related thing is something like uh after you you do once you you get used to to doing all the time. Uh so it's kind of a standard. Uh have you considered in Django to like Find a a way that it's automatically added, like in just a like a list view or something like that

Speaker 1: Yes, so there's various talk about this. Um so there's a package by uh Simon Charette, who's one of the um main main contributors to the RM that does this already. It's called something Adam help me out when it's called what's it called? Uh

Speaker 4: Simon's package is Django seal and it it raises a warning on the lazy foreign keys. It's the package I maintained with Gordon that does the automatic adding of prefetch and select relators.

Speaker 1: There you are. Yeah, so there you are. What's the all one calldown?

Speaker 4: Django Auto Prefetch. I'll post the link to it.

Speaker 1: What a great name, Django Auto Prefetch. There's also some talk about this around the whole async ORM project because This lazy prefetching or this lazy attribute lookup that will never work with async. So if if you do that, you're always going to hit a problem. So if you are gonna if we are gonna have an async iterator of a of a query set, then you're going to have to have used CERC related or. um prefet related to get whatever models you want need to instantiate when rendering that when iterating that query set. So there's going to be a better story about it. Whether that's in core or third party packages or I can't say, but there's Adams package, there's Simon's package, which are all great.

Speaker 3: Thanks. I'd like those names uh later.

Speaker 1: Okay. Well, Adam can type them into the chat here now, can't you?

Speaker 3: Thank you.

Speaker 1: Let's have a look. I was just trying to get back into my talk thing on the um loud swarm to see if there were any questions in the chat box there. Hang on, I'm just scrolling down. Let's see Yeah, so Adam's actually put that in the um He's put Adam's put a link to the Django Auto Prefetch in the Slack by the looks of it.

Speaker 3: Well, uh another thing on that topic, um what about only queries? Uh I mean Normally when you define a serializer and you choose the fields you want, they could also be automatically added to OnlyQuery, so you don't have to fetch the whole table Uh for that. So is there any solution for that?

Speaker 1: To be honest, no, not not built not like Django 's not like the RM can't be psychic, right? It it it does in a sense You build a view, you look at the queries, you see what data you're going to use, you know your data set. If you need to put any deferred fields in there, sorry , only skip only these fields, or a defer is to say exclude these fields. It's like um the fields and exclude arguments on mo on um a model form, but like equivalent at the RM level, uh the fetch level. To I d I don't think that it's a A big ask for the developer to have to specify those fields themselves is what would be my kind of first take on that. I think

Speaker 1: Maybe there's some optimizer out there that can magically you know read your code and understand it and know it and maybe that will happen with, you know AI going the way it is or machine learning going the way it is. Maybe in the future that'll happen. But I think at this stage of the of the art, a developer needs to optimize their queries

Speaker 3: Yeah.

Speaker 1: I mean I'm not I'm not a massive alt-REM expert, so I that's just but that's just my feeling Adam, I can't hear you. I can see your lips moving, but I can't actually hear you Yeah. Let's do it in mine. Wait, I 'm just gone. Any other questions, thoughts? Anyone

Speaker 5: Yeah, if you if you did have a list with seventeen thousand choices in there. Have you got any good uh options to navigate your way through that?

Speaker 1: No, that's an awesome question because my actual kind of preference here is to render the select list, even though it's got 17,000 in it and it's far too many, is to render it into the HTML and then progressively enhance it with on this client side with a little bit of JavaScript to add you know, a a a um a search box or whatever you want. Um so an autocomplete because the trouble with so the Django admin has has autocomplete fields. So the the worst option is raw ID fields, right? So back in the day We the Django had admin has had this problem forever in that you've got 17,000 records and you can't possibly deal with that in a select box. So what should you do? Use raw ID for where you have to type in the number. But then you have to go and look in the database, find the ID, and then put it into the field.

Speaker 1: That's not sustainable. So a few versions ago they added all to complete fields, which is great. It's like this nice select to widgety thing and you type in and it will la dynamically go and make these Ajax requests back to the um back to the admin. We're looking up the records and find you ones that match. It works really well, but you've got this constant overhead of um uh the the network calls when you you could for most query sets you could just rent you could just render the choices and then use keep it totally client-side that's so that's my preference my preference is to um is to use some kind of JavaScript to take the select widget and progressively enhance it into something that's more user-friendly when you've got lots of records.

Speaker 5: Right.

Speaker 1: If you can't send it all to the client, then yeah, you have to back that with an extra API call of some kind. you know an an API call that goes back and fetches the data live so an auto live autocomplete. So they're the two main options and then you know maybe there are different patterns. But If your list is alphabetically sorted, like so for countries, you quite often you've had to do checkout forms or you've had to fill in your country and you pick it from a big long list. It's like, you know, it's it's long. There's a lot of countries. But it's not too hard to find yours because you can open the select box, you can press S for Spain, and you get down to the S's, and then you've only got half a dozen S's to click through. So for me It depends on your data set, but I'd always start with a with a drop-down if you can. Why? Because if your JavaScript fails to load, it still works.

Speaker 1: It might not be great, but it still works. Whereas other solutions which require the JavaScript, if your JavaScript doesn't lo load, then you'll you it your site's broken at that point. And over mobile connections and all the rest, your user can't use your site at that point. So that's my view.

Speaker 5: Great. Thanks.

Speaker 1: No, thank you.

Speaker 5: Great, great talk as well. Really enjoyed it. And looking through the slides will be really useful. So thanks.

Speaker 1: Yeah, okay. I'll put I will make sure I put those up. I'll print them off as PDF and put it in the Slack because that's

Speaker 5: Brilliant