django query large table in chunks

0 votes
214 views
asked Apr 4 by anonymous

I have a some tables with several million rows in it, sometime my db is down and other times i run into memory errors while querying.

I am processing all records so i must select all the rows

right now i am doing something like this.

MyModel.objects.filter(**criteria)[1:100]
MyModel.objects.filter(**criteria)[100:200]

and then making a dict by combining result from different querysets, but offcourse this is not suitable and generic, need a safe solution.

1 Answer

0 votes
answered Apr 4 by anonymous

There may me many reason why your query is slow, may it is due to indexes or ur server configuration or some thing else.

to query large table and queryset records into no of small queryset you try something like this

def splited_queryset(queryset, chunk_size):
    start_pk = 0
    queryset = queryset.order_by('pk')
    while True:
        if not queryset.filter(pk__gt=start_pk).exists():
            break

        try:
            # Fetch chunk_size entries if possible
            end_pk = queryset.filter(pk__gt=start_pk).values_list(
                'pk', flat=True)[chunk_size - 1]

            # Fetch rest entries if less than chunk_size left
        except IndexError:
            end_pk = queryset.values_list('pk', flat=True).last()

        yield queryset.filter(pk__gt=start_pk).filter(pk__lte=end_pk)

        start_pk = end_pk
Welcome , qPocho is an online community for programmers share there knowledge.
...