How can I improve pagination performance in multiple queryset django?

Question

I have 2 queryset :

queryset_primary = PrimaryUserSerializer(FileUpload.objects.all().order_by('name'), many=True, context=context).data
queryset_secondary = MemberSerializer(Members.objects.all().order_by('member_name'), many=True, context=context).data

Both having different keys ..so that I iterated both querysets :

    response = []
    for primary in queryset_primary:
        # name_pri=primary['primary_user_id']
        new_data={ 
            'user_id' : primary['primary_user_id'],
            'name': primary['name'],
        }
        response.append(new_data)

    for secondary in queryset_secondary:
        new_data={
            'user_id' : secondary['secondary_user_id'],
            'name': secondary['member_name'],
        }

Again I used a common serializer having similar keys in it, for pagination :

    responses = self.paginate_queryset(response)
    if responses is not None:
        serializer = CommonUserSerializer(responses,many=True)
        data = {
            'code': 200,
            'status': "OK",
        }
        page_nated_data = self.get_paginated_response(serializer.data).data
        data.update(page_nated_data)
        data['response'] = data.pop('results')
        return Response(data)

It totally taking 8 seconds of loading time.

How can I reduce the API loading time ?

Hi Jisha, hopefully my answer gets you someway to improve your API's efficiency, let me know how you get on. — Micheal J. Roberts, Feb 07 '20 at 13:18

score 0 · Answer 1 · answered Feb 07 '20 at 11:42

When exposing bulk data, models and serializers should be avoided where possible. Have a look at Django's ORM .values() option to make a specific select. Try to construct your own structures for json serialization. I'd also avoid embedding nested data in your structures (especially in list views). It might turn out much cheaper to have a client call twice or filter your api then returning 'complete' data at once.

Have you tried to profile the specific api call? Django's debug toolbar is a convenient tool to pin point the bottleneck.

Micheal J. Roberts · Answer 2 · 2020-02-07T13:17:29.690

Have a look at .values() in the ORM:

https://docs.djangoproject.com/en/3.0/ref/models/querysets/#values

Members.objects.all().order_by('member_name').values()

This should give you a QuerySet that returns dictionaries, rather than model instances, when used as an iterable.

The you could couple this with:

from django.http import JsonResponse

def some_view(request):
    data = list(Members.objects.all().order_by('member_name').values()) # Use list(), because QuerySet is not JSON serializable by default!
    return JsonResponse(data, safe=False)

More specific to your question:

from rest_framework import status
from rest_framework.response import Response

def some_view(request):
    merged = []

    merged.append(
        list(Members.objects.all().order_by('member_name').values('user_id'. 'name')) + list(FileUpload.objects.all().order_by('name').values('user_id'. 'name'))
    )

    responses = self.paginate_queryset(merged)
    if responses is not None:
        serializer = CommonUserSerializer(responses, many=True)

        data = {
            'success': true,
        }
        paginated_data = self.get_paginated_response(serializer.data).data
        data.update(paginated_data)
        data['response'] = data.pop('results')
        return Response(data, status=status.HTTP_200_OK)

You will also need to do some annotations() to get your user_id attributes to primary_user_id and secondary_user_id

Much more efficient!

How can I improve pagination performance in multiple queryset django?

2 Answers2