retrieving 1000s of rows performace


I have roughly 12000 rows in a table, the table has 3 fields, Product Name. Product Id and Product Price.
I’m hoping to display this data on pages of 10etc. This works but it’s sooooo slow to render the table.

I’m just doing {% for product in products %} which means the HTML is huge.

What’s the correct way to show this, would it be best to make individual calls for each page limited to batches of 10? How would a search box work if a user was searching for a product in the entire table?

I’m thinking the search would be a separate call? and then somehow render that single item in the table, but would that mean reloading the page?

Or should I be doing this is AJAX?

Just after some advice really?



Hi Tom,

You are right, that everything is very slow because of the huge HTML you are generating.

You can get an idea how Django is solving this problem with built-in admin interface.

If you want to get more fancy without new page rendering, you can have a look at something like django-htmx.

Take a look at the Django pagination facility. See Pagination | Django documentation | Django

Thanks, Ken, so would this be like creating a single HTML page with say 10 items in a table, and then rather than pages in the table, it’s through different HTML pages?

On that basis, it would be better to use this method using {% includes table.html %} kind of thing?

Yes. It doesn’t matter whether the output is a table, or a block of html, JSON elements being retrieved by an AJAX call, or pretty much anything else. The paginator manages the modifications to the URL and queryset for the instances over which the view is iterating.

Not necessarily.

If you read the docs and example, you’ll see that the functionality you add with the paginator is added to your existing page, it doesn’t change your page. It modifies the queryset being used by the view to create a “window” (informal use of the word) showing only the current page’s worth of data.
(And of course, you can always read the source code for it to see how it works.)

I’ve tried the pagination, but the problem with this is I can’t search and filter, as it’s only showing the data for the current query set.

The existing data table enables the list to be filtered using javascript i think. So that would have to be written to make a call to a url to retrieve the actual search item, rather than searching the html?

Also with the pagination, it seems that it reloads the entire page rather than the content of the table - is that correct, or am I doing it wrong?

If you’re “searching and filtering” what’s on the page, that’s correct. That’s the purpose of pagination.

If your searches and filters are for the entire set of data, then those would need to become new queries.

That depends upon the JavaScript (or JavaScript library) you’re using. (Which, btw, you never mentioned as being a factor here. That may make a difference regarding the original answer.)

That is correct.

I was looking at the JavaScript Datatables, which seemed nice because of the filtering/searching and the way it retrieves the product as you type within the search box.

What I’m trying to achieve is the ability to add products to a watchlist as with nice UX as possible.

This isn’t quite an accurate statement of what it’s doing. It’s not “retrieving” anything. It’s filtering what has already been provided, which by default does make it incompatible with the default Django paginator.

If you’re working with datatables, you absolutely want to change both your view and your datatables definition to work with Django as a JSON source. You do not want to have Django render the data as html to be sent to datatables, to have it rerendered as a datatables table.

What we do is have the page defined with the datatable specifying an AJAX source, referring to a different view responsible for returning the data. This allows the browser to render the page, showing the “skeleton” of the table while the second view is retrieving the data.

You’ll also want to look at the Server-side processing for allowing datatables to work with Django. There’s a corresponding Django package, django-datatables-view to facilitate that from the Django side.

(We use datatables extensively in our systems - it’s our default mechanism for any tabular display. We use/abuse it in every way imaginable.)

Does this mean rather than returning the render it returns json from the view?

That sounds really good. I want to do the same.

I’m going to work through the examples, but no doubt I’ll be back… :wink:



For clarity, a view always returns a response.

The output of the render function is an HttpResponse object.

So yes, instead of returning an HttpResponse, your second view being used to return JSON to datatables would return a JsonResponse.

Hi Ken,

I’ve taken the example code and tried to recreate it, but it’s not working for me.

The page renders with the column names, but no data is being displayed from my model.

in the example is asking to set the urls to

 url(r'^testmodel$', TestModelList.as_view(), name="testmodel"),
 url(r'^testmodel_data/$', TestModelListJson.as_view(), name="testmodel_list_json"),

This syntax is not something I’ve used before - my URLs are formatted:

    path('testmodel', TestModelList.as_view(), name="testmodel"),
    path('testmodel_data', TestModelListJson.as_view(), name="testmodel_list_json"), 

This is probably a stupid question, but by not using is this r'^testmodel$' break it?

I think the r'^ is regex? but want is the $ doing?


First, whatever isn’t working has nothing to do with the URLs. (Other than possibly you missing the trailing slash in your url paths - but whether that’s a problem depends upon the url being invoked.)

It’s the url( function that specifies that the first parameter is a regex.

The r at (r' indicates that the string is a raw python string.

In a regex, the carat - ^ indicates the beginning of the string while the dollar sign - $ indicates the end of the string. In other words, those urls will only match with those exact strings. If the URL contains anything before or after those strings, they won’t match.

I don’t know what sample code you’re referring to so I can’t help much beyond that.

Thank you Ken.

I have got this working now, it was an issue with the javaScript.

Im using these views

class ProductListJson(BaseDatatableView):
    model = Product
    columns = ['Product_name','Product_symbol','Product_price']
    order_columns = ['Product_name','Product_symbol','Product_price']
    max_display_length = 500

    def render_column(self,row,column):
        return super(ProductListJson, self).render_column(row, column)

    def filter_queryset(self,qs):
        search = self.request.GET.get('search[value]',None)
        if search:
            qs = qs.filter(name__istartswitch=search)

        return qs

class TestModelList(TemplateView):
    template_name = 'datatabletest.html'

class TestModelListJson(BaseDatatableView):
    model = Product

When the table is rendered with the data, I’d like to make a request to an API to return the current price of the products being rendered.

I’ve not worked with class-based views before. So would I add my def name within the TestModelListJson class view? as this is the url being calling by AJAX i think

And how do I know what data is being displayed in order to pass in the product symbol to get the price. The Product symbol is used to retrieve the data from the api


Confirming - then there is a connection between this topic and Loop through queryset and store data?

Are you making an individual API call for each product? If so, and you’re talking about 1000+ rows, then nothing is going to significantly speed this up.

Or, are you retrieving all current prices for all products in one call? If so, then what you’ve got is a “matching” situation. (Match items from list of ObjectA with items in list of ObjectB)

Regarding working with CBVs, see my response at Loop through queryset and store data. There is a bit of a learning curve with them - they do require some comfort with class inheritance in Python to really understand how they work and to fully take advantage of them.

You’re going to need to start understanding CBVs before I can even begin to address this properly. Briefly, there’s a function in the class called get_context_data that is responsible for pulling all the data together to be rendered by the view. You will end up overriding that method and doing additional work on that context. (And if that sentence doesn’t make any sense yet, that’s ok - it will.)

Yes, so making the call and adding the data into a dictionary is cool, but this is going to be a single loop for what is being displayed in the data table. Which I will limit to 10 items at a time. So i will need to know the 10 items to run the for loop through.

So i was thinking it would be something like:

for product in qs:
    do something 

It’s knowing what qs should be is where I’m confused, which is probably what you are talking about here

Unfortunately, it’s 1 call per product, which is why I think it was better to limit the list to 10 products, I know it will make 10 calls per pagination, but I think this should be ok.

I reckon 9 out 10 times the user will search for an item rather than click through pages, but id like the prices to be shown on the page if they do click.

I’ll spend some time trying to understand the class base views.


Correct. After you get a basic understanding of CBVs, then your next step would be to look at the source code for django-datatables-view, specifically the BaseDatatableView - and the classes that it is built from, to see how the data to be rendered is retrieved and gathered.

Keep in the back of your mind that as you dig deeper into this, you’re going to want to supplement the data being returned, not replace. It’s not any more difficult to do this, just a little different.

Ok looking at the source code, I kinda understand what is happening.

I think ultimately the data is presented via these functions:

def prepare_results(self, qs):
        data = []
        for item in qs:
            if self.is_data_list:
                data.append([self.render_column(item, column) for column in self._columns])
                row = {col_data['data']: self.render_column(item, col_data['data']) for col_data in self.columns_data}

        return data


def get_context_data(self, *args, **kwargs):
            self.initialize(*args, **kwargs)


So I think i need a way to inject the data i need into the data list?

Yep, you’ve got it.

You’ve got a couple different ways to approach this, but it’s probably more a case of modifying the elements of the data list returned than it is of adding data to it.

If I were doing it, I’d probably create my own prepare_results method that calls super() to get the “default” data response, and then iterate over those results and augment each row with whatever additional data is needed.

Ah okay.

This sounds like a good approach, i will take a look at this. I’m assuming i would need to pass the data back to the original class?

So would I create a new function within 1 of the existing classes or use a separate function calling the class?

def prepare_results(TestModelListJson):
 do something