Django formset , queries for relational field for every form

Question

Models.py

class Material(BaseModelClass):
    material = models.CharField(max_length=25, verbose_name='Material')
    def __str__(self):
        return self.material

class PurOrder(BaseModelClass):
    order_number = models.CharField(max_length=25)

class PurOrderItem(BaseModelClass):
    order = models.ForeignKey(PurOrder, on_delete=models.CASCADE)
    material = models.ForeignKey(Material, on_delete=models.PROTECT)

I created a PurOrder form and PurOrderItem formset

PurOrderForm = modelform_factory(PurOrder, fields=('order_number',))
PurOrderFormset = inlineformset_factory(PurOrder, PurOrderItem,fields=('material',))

Initialized them as follows.

form = PurOrderForm(instance=order_instance)
queryset = order_instance.purorderitem_set.all().select_related('material',)
formset = PurOrderFormset(instance=order_instance, queryset=queryset)

This setup costs me 22 queries if there is 20 PurOrderItem for selected purorder.

1 for PurOrder instance,
1 for PurOrderItem instance
20 for selected materials for those PurOrderItem's.

Think of it, if there is 1000 PurOrderItem

With the provided select_related, it add's material to PurOrderItemselect, but when it comes to display it I think, it query again.

I use django-autocomplete-light, so it saves me from querying all material instances, but it keeps querying selected material, to display it even though I select_related material.

Ideally, I would select PurOrder instance with prefetched purorderitem and related materials, these means 3 queries. Prefetched purorderitem's and material's will be used, when it's their turn.

Please advice me a way to avoid selected choices query.

Note: I try to avoid caching here.

UPDATE

Long time after I created this question and I tried provided solutions. Problem is, formset's forms are not aware of each other. Therefor, provided queryset's selected_related or prefetch_related lookups aren't passed to the formset forms.

How are you using `django-autocomplete-light`? I don't see any dal widget in your `PutOrderFormset`. — Antoine Pinsard, Mar 18 '18 at 12:09
I thought its irrelevant to question and omit adding dal widget codes. — durdenk, Mar 19 '18 at 17:23
I think the answer to this question is what you are looking for: https://stackoverflow.com/questions/15203207/prevent-django-from-querying-for-foreignkey-options-for-every-form-in-modelforms — solarissmoke, Mar 20 '18 at 04:40
I think you could use `prefetch_related` function: https://docs.djangoproject.com/en/2.0/ref/models/querysets/#django.db.models.query.QuerySet.prefetch_related — Dan Lupascu, Mar 20 '18 at 07:07
@durdenk Reading your update, that mean that if you have many forms rendering is not a good idea because you are hiting your database so much, I'm right? — ozo, Aug 11 '19 at 00:52

score 5 · Answer 1 · answered Mar 25 '18 at 08:37

You are fine. This code will cost you only 3 queries. As you can see in select_related() documentation:

Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query. This is a performance booster which results in a single more complex query but means later use of foreign-key relationships won’t require database queries.

It means that your code will preform a mysql join and will result a dataset with all of the data. So by that, I can see that your code is pretty good.

I suggest you use some kind of profiling like django-silk to see how many queries are being generated.

Footnote:

As you can see in prefetch_related() documentation, the difference between prefetch_related() and select_related() is in the way they preform the join:

This (prefetch_related) has a similar purpose to select_related, in that both are designed to stop the deluge of database queries that is caused by accessing related objects, but the strategy is quite different.

...

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query.

...

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python.

So as long as you need one-to-one relationship, select_related is the most efficient way to query the relationship.

Gal, did you use this answer for yourself? I couldnt get it working. Django formsets aren't aware of each other not base_model. Therefore provided select_related's and prefetch arent passed to formsets. This is what I experienced. — durdenk, Jul 14 '18 at 20:41

score 0 · Answer 2 · answered Aug 11 '19 at 01:27

0

I recently realized that the problem in a similar case was not as such the formsets, the problem was the {{formset.errors}} in the template, since each formset generated one query for this.

answered Aug 11 '19 at 01:27

ozo

883
1
10
18

Django formset , queries for relational field for every form

2 Answers2