# retrieving 1000s of rows performace

**URL:** <https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849>\
**Category:** Forms & APIs\
**Created:** [January 28, 2022, 10:48am UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849 "2022-01-28T10:48:51Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 28, 2022, 10:48am UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/1 "2022-01-28T10:48:52Z")

</div>

Hi,

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?

Thanks

Tom

---

<div class="post-metadata">

**Author:** ![Lars147](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/lars147/32/7209_2.png) [@Lars147](https://forum.djangoproject.com/u/Lars147)\
**Post date:** [January 28, 2022, 11:25am UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/2 "2022-01-28T11:25:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 28, 2022, 12:22pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/3 "2022-01-28T12:22:21Z")

</div>

Take a look at the Django pagination facility. See [Pagination | Django documentation | Django](https://docs.djangoproject.com/en/4.0/topics/pagination/#pagination)

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 28, 2022, 6:03pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/4 "2022-01-28T18:03:14Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 28, 2022, 7:53pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/5 "2022-01-28T19:53:27Z")

</div>

> [@TommyQuality](#):
>
> so would this be like creating a single HTML page with say 10 items

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.

> [@TommyQuality](#):
>
> better to use this method using `{% includes table.html %}` kind of thing?

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.)

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 30, 2022, 12:14pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/6 "2022-01-30T12:14:38Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 30, 2022, 12:31pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/7 "2022-01-30T12:31:11Z")

</div>

> [@TommyQuality](#):
>
> 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.

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.

> [@TommyQuality](#):
>
> So that would have to be written to make a call to a `url` to retrieve the actual search item,

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.)

> [@TommyQuality](#):
>
> 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?

That is correct.

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 30, 2022, 3:21pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/8 "2022-01-30T15:21:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 30, 2022, 3:38pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/9 "2022-01-30T15:38:15Z")

</div>

> [@TommyQuality](#):
>
> which seemed nice because of the filtering/searching and the way it retrieves the product as you type within the search box.

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](https://datatables.net/examples/server_side/) for allowing datatables to work with Django. There’s a corresponding Django package, [django-datatables-view](https://pypi.org/project/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.)

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 30, 2022, 4:30pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/10 "2022-01-30T16:30:20Z")

</div>

> [@KenWhitesell](#):
>
> 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.

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

> [@KenWhitesell](#):
>
> 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.

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… 😉

Cheers

Tom

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 30, 2022, 5:36pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/11 "2022-01-30T17:36:22Z")

</div>

> [@TommyQuality](#):
>
> Does this mean rather than returning the render it returns `json` from the view?

For clarity, a view **always** returns a response.

The output of the [render function](https://docs.djangoproject.com/en/4.0/topics/http/shortcuts/#render) is an HttpResponse object.

So yes, instead of returning an [HttpResponse](https://docs.djangoproject.com/en/4.0/ref/request-response/#httpresponse-objects), your second view being used to return JSON to datatables would return a [JsonResponse](https://docs.djangoproject.com/en/4.0/ref/request-response/#jsonresponse-objects).

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 30, 2022, 9:26pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/12 "2022-01-30T21:26:25Z")

</div>

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

```auto
 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:

```auto
    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?

Thanks

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 30, 2022, 9:33pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/13 "2022-01-30T21:33:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 31, 2022, 11:48am UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/14 "2022-01-31T11:48:32Z")

</div>

Thank you Ken.

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

Im using these views

```auto
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

Thanks

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 31, 2022, 2:30pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/15 "2022-01-31T14:30:09Z")

</div>

Confirming - then there is a connection between this topic and [Loop through queryset and store data](https://forum.djangoproject.com/t/loop-through-queryset-and-store-data/11763)?

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](https://forum.djangoproject.com/t/loop-through-queryset-and-store-data/11763). 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.

> [@TommyQuality](#):
>
> And how do I know what data is being displayed in order to pass in the product symbol to get the price.

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.)

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 31, 2022, 2:56pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/16 "2022-01-31T14:56:39Z")

</div>

> [@KenWhitesell](#):
>
> Confirming - then there is a connection between this topic and [Loop through queryset and store data](https://forum.djangoproject.com/t/loop-through-queryset-and-store-data/11763)?

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:

```auto
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  
`get_context_data`

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.

Thanks

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 31, 2022, 3:21pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/17 "2022-01-31T15:21:23Z")

</div>

> [@TommyQuality](#):
>
> It’s knowing what `qs` should be is where I’m confused, which is probably what you are talking about here

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.

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [January 31, 2022, 4:29pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/18 "2022-01-31T16:29:42Z")

</div>

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

I think ultimately the data is presented via these functions:

```auto
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])
            else:
                row = {col_data['data']: self.render_column(item, col_data['data']) for col_data in self.columns_data}
                data.append(row)

        return data

```

and

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

...

```

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

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [January 31, 2022, 5:25pm UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/19 "2022-01-31T17:25:08Z")

</div>

> [@TommyQuality](#):
>
> I think ultimately the data is presented via these functions:

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.

---

<div class="post-metadata">

**Author:** ![TommyQuality](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/tommyquality/32/6302_2.png) [@TommyQuality](https://forum.djangoproject.com/u/TommyQuality)\
**Post date:** [February 1, 2022, 10:54am UTC](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849/20 "2022-02-01T10:54:10Z")

</div>

Ah okay.

> [@KenWhitesell](#):
>
> 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.

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?

```auto
def prepare_results(TestModelListJson):
 do something

```

[Next page](https://forum.djangoproject.com/t/retrieving-1000s-of-rows-performace/11849.md?page=2)