Slow Performance for a data size as less as 100

[gaurav08suri]

List for getting participants is extremely slow. For data as low as 400, the latency is almost 15 seconds. On enabling the debug logs, it was found that there is N + 1 query problem faced. For each EventParticipant model, a Participant query is fired, lazily loaded. Links here : https://hakibenita.com/django-rest-framework-slow http://ses4j.github.io/2015/11/23/optimizing-slow-django-rest-framework-performance/

One issue was solved using eager-fetching, This brought down the latency to 4 seconds, which is great improvement over the previous one but still very slow.

Looking to more ideas to solve this.

[gaurav08suri]

@roguesherlock - Do share your views here

[gaurav08suri]

Lazy loading solved using eager fetching in this commit https://github.com/gaurav08suri/mhtportal-api/commit/d1b55d2a7929d5a361236cf6f5ce2f1e5c798cd7

[roguesherlock]

I think what you're looking for is select_related https://docs.djangoproject.com/en/2.2/ref/models/querysets/#select-related

Can you try this and let me know?

    queryset = EventParticipant.objects.select_related('participant').all()

[gaurav08suri]

This still takes the same amount of time...no observable performance gain...

[roguesherlock]

okay so we'd need to figure out where exactly the bottleneck occurs. Is it the query itself? or is it that database doesn't have enough resources ? or is it the storage ? I'm not sure 😐

[gaurav08suri]

Any idea, how we do that ? Do we need to profile the app ? Any way we can do so ?

[gaurav08suri]

One thing, I need to try out is connection pooling

[gaurav08suri]

Setting the CONN_MAX_AGE is really helpful...65d3281

[roguesherlock]

I don't currently know but I'l take a look

[gaurav08suri]

Closing this issue. Most of the above things helped to improve the performance