How to prevent a row count from being sent to the db when paginator is disabled

Question

I'm using Django 1.11, Django-tables2 1.19.0 with MS SQL via django-pyodbc-azure 1.11.0.0.

I'm showing a table for a model that is for a rather slow view which unfortunately must run sub-queries for every row. I can't change the view, and so I've disabled the paginator for tables2 and done a slice to limit the number of rows the view subqueries will run on:

def my_view(request):
    queryset = my_model.objects.all()
    table = my_table(queryset.all()[:25])  # <- only get top 25
    RequestConfig(request, paginate=False).configure(table)
    return render(request, 'my_template.html', {'table': table})

I also disabled ordering for the columns.

That works, however, when using django-admin-toolbar, I can see that a "...COUNT_BIG(*) FROM (SELECT TOP 25..." is nevertheless being sent to the server.

I want to prevent that COUNT_BIG if possible for performance reasons, but my various misguided attempts over the last few days have all met with horrible failure.

I'd really appreciate any help with this one. Thanks.

remove limits from querysets and user select related. Use Pagination is in one hit return limited objects. — SuReSh, Mar 12 '18 at 03:56
Thanks very much Suresh, yes, when using pagination it does return limited objects, but it also does a COUNT_BIG so that it knows what the total number of objects are. However, when it does the COUNT_BIG, because the underlying view is using sub-queries for every row and the table is very big, it takes a very long time to get the count. After that, the actual select that brings back the limited objects is fast. So i'm trying to get it not to do the COUNT_BIG, which i thought it was doing in order to work out the pagination.. — fzzylogic, Mar 12 '18 at 04:00
@fzzylogic interesting, I think this should be considered a bug in django-tables2. I've just created a test case to reproduce it: https://github.com/jieter/django-tables2/issues/551 — Jieter, Mar 12 '18 at 09:17

score 3 · Accepted Answer · answered Mar 12 '18 at 16:52

3

This was a bug in django-tables2 and should be fixed with commit 942f05e.

The fix is released as version 1.21.1.

answered Mar 12 '18 at 16:52

Jieter

4,101
1
19
31

score -1 · Answer 2 · answered Mar 12 '18 at 04:04

from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger


class PaginateQueryset(object):
    '''Paginate Queryset'''

    def paginate_query_set(self, queryset, page_size):
        ''' Paginate '''
        page = self.search_params.get('page', 1)
        paginator = Paginator(queryset, page_size)
        try:
            objects = paginator.page(page)
        except PageNotAnInteger:
            objects = paginator.page(1)
        except EmptyPage:
            objects = paginator.page(paginator.num_pages)
        return objects, paginator.count

def my_view(request):
     queryset = my_model.objects.all()
     result, count = PaginateQueryset.paginate_query_set(
                queryset, 25)
     return result, count

Appreciate your feedback, thanks. django-tables2 already handles pagination automatically and correctly, but i want to find a way to override the count, because it looks like it's because of the need to count for paging that a COUNT_BIG instruction is sent to MS SQL. So i want to not use paging and tell the system not to ask for a row count. But maybe that is the wrong approach and maybe i'm trying to do it in the wrong place.. — fzzylogic, Mar 12 '18 at 04:48

How to prevent a row count from being sent to the db when paginator is disabled

2 Answers2