Optimize Django Rest ORM queries

Question

I a react front-end, django backend (used as REST back). I've inherited the app, and it loads all the user data using many Models and Serializes. It loads very slow. It uses a filter to query for a single member, then passes that to a Serializer:

found_account = Accounts.objects.get(id='customer_id')
AccountDetailsSerializer(member, context={'request': request}).data

Then there are so many various nested Serializers:

AccountDetailsSerializers(serializers.ModelSerializer):
   Invoices = InvoiceSerializer(many=True)
   Orders = OrderSerializer(many=True)
   ....

From looking at the log, looks like the ORM issues so many queries, it's crazy, for some endpoints we end up with like 50 - 60 queries.

Should I attempt to look into using select_related and prefetch or would you skip all of that and just try to write one sql query to do multiple joins and fetch all the data at once as json?
How can I define the prefetch / select_related when I pass in a single object (result of get), and not a queryset to the serializer?
Some db entities don't have links between them, meaning not fk or manytomany relationships, just hold a field that has an id to another, but the relationship is not enforced in the database? Will this be an issue for me? Does it mean once more that I should skip the select_related approach and write a customer sql for fetching?
How would you suggest to approach performance tuning this nightmare of queries?

In the specific case you cited, are you processing just one Account at a time (as implied by the code) or several (with repeated get() queries)? — Endre Both
– Endre Both, Commented Mar 25, 2019 at 7:27
@EndreBoth One account at a time, with many related nested modals — Dory Zidon
– Dory Zidon, Commented Mar 26, 2019 at 9:29
Then you must have a really complex hierarchy to get bogged down – check whether you really need all the nested stuff up front or if you can load them on demand. — Endre Both
– Endre Both, Commented Mar 26, 2019 at 9:36
ya I'm going through that process but in the meanwhile I'd prefer to make joins where I can...How can you provide prefetch / select related for a get query? as that returns a model, not queryset? — Dory Zidon
– Dory Zidon, Commented Mar 26, 2019 at 9:40
If you replace the get with a filter(...)[0], you end up with the same object, but you can implement any prefetches or preselects. But this is not much different from Jens's proposed approach. — Endre Both
– Endre Both, Commented Mar 26, 2019 at 9:48

jensmtg · Accepted Answer · 2019-03-24 18:58:50Z

3

I recommend initially seeing what effects you get with prefetch_related. It can have a major impact on load time, and is fairly trivial to implement. Going by your example above something like this could alone reduce load time significantly:

AccountDetailsSerializers(serializers.ModelSerializer):

    class Meta:
        model = AccountDetails
        fields = (
            'invoices',
            'orders',
        )

    invoices = serializers.SerializerMethodField()
    orders = serializers.SerializerMethodField()

    def get_invoices(self, obj):
        qs = obj.invoices.all()\
            .prefetch_related('invoice_sub_object_1')\
            .prefetch_related('invoice_sub_object_2')
        return InvoiceSerializer(qs, many=True, read_only=True).data

    def get_orders(self, obj):
        qs = obj.orders.all()\
            .prefetch_related('orders_sub_object_1')\
            .prefetch_related('orders_sub_object_2')
        return OrderSerializer(qs, many=True, read_only=True).data

As for your question of architecture, I think a lot of other factors play in as to whether and to which degree you should refactor the codebase. In general though, if you are married to Django and DRF, you'll have a better developer experience if you can embrace the idioms and patterns of those frameworks, instead of trying to buypass them with your own fixes.

edited Mar 24, 2019 at 18:58

answered Mar 24, 2019 at 18:50

jensmtg

5065 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dory Zidon Over a year ago

But how can I use the prefetch_related and select_related when I don't return a queryset, but I return a get..meaning I do Account.objects.get(filter). I would like the account to already get all the related info in one query if possible..so when I pass an object to the first serialieer? meaning Accounts.objects.filter(<filter>), how do I ensure that all the serializer methods will use the prefetch? Should I just add prefetch before the get? or how do you handle that?

Endre Both · Accepted Answer · 2019-03-26 09:47:22Z

2

There's no silver bullet without looking at the code (and the profiling results) in detail.

The only thing that is a no-brainer is enforcing relationships in the models and in the database. This prevents a whole host of bugs, encourages the use of standardized, performant access (rather than concocting SQL on the spot which more often than not is likely to be buggy and slow) and makes your code both shorter and a lot more readable.

Other than that, 50-60 queries can be a lot (if you could do the same job with one or two) or it can be just right - it depends on what you achieve with them.

The use of prefetch_related and select_related is important, yes – but only if used correctly; otherwise it can slow you down instead of speeding you up.

Nested serializers are the correct approach if you need the data – but you need to set up your querysets properly in your viewset if you want them to be fast.

Time the main parts of slow views, inspect the SQL queries sent and check if you really need all data that is returned.

Then you can look at the sore spots and gain time where it matters. Asking specific questions on SO with complete code examples can also get you far fast.

If you have just one top-level object, you can refine the approach offered by @jensmtg, doing all the prefetches that you need at that level and then for the lower levels just using ModelSerializers (not SerializerMethodFields) that access the prefetched objects. Look into the Prefetch object that allows nested prefetching.

But be aware that prefetch_related is not for free, it involves quite some processing in Python; you may be better off using flat (db-view-like) joined queries with values() and values_list.

edited Mar 26, 2019 at 9:47

answered Mar 24, 2019 at 19:35

Endre Both

5,8201 gold badge30 silver badges32 bronze badges

1 Comment

Dory Zidon Over a year ago

but how can I use the prefetch_related and select_related when I don't return a queryset, but I return a get..meaning I do Account.objects.get(filter), not Accounts.objects.filter(<filter>), how do I ensure that all the serializer methods will use the prefetch? Should I just add prefetch before the get? or how do you handle that?

Collectives™ on Stack Overflow

Optimize Django Rest ORM queries

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related