Index column slows down table with large number of rows

bg7

This example shows how to create an index column.

https://datatables.net/examples/api/counter_columns.html

If you increase the number of rows significantly the table starts to struggle. Typing in the search box results in noticeable lag. This example I created has 2k+ records. I just duplicated the sample data over and over. I tried to create 10k records but the live.datatables.net editor said it was too big to save.

https://live.datatables.net/sopifixu/1/edit

We've got tables that have closer to 10k records and they're very slow to respond to searching and ordering as a result. Is there a way to implement the index column so that it performs better?

We had an index column that worked well in DataTables 1.x but it was built differently.

    dt.on('order search', () => {
        dt.column('index:name', { search: 'applied', order: 'applied' }).nodes().each(function (cell, i) {
            cell.innerHTML = i + 1;
        });
    }).columns.adjust().draw('full-hold');

That code doesn't work with DataTables 2.x. Ordering, searching and paging results in the numbers in the index column being completely out of order or not showing up at all.

Thanks.

Ben

allan

Hi Ben,

The reason that will be slow is that you are writing to all of those cells all at once. What would be better for this is just writing the numbers for the current page (e.g. 10 rows at a time).

I'd suggest using draw and write to the visible cells on every draw (relatively inexpensive if its just 10 rows or so). Use dt.column('index:name', {page: 'current').nodes() to get the cells to update and page.info() to make sure you start the index at the correct number for the current page!

Regards,
Allan

kthorngren

One option is to use selector-modifier of page: "current" so cells().every() only iterates the current page. This will require the use of page.info to calculate the starting index for the page. Also the page event will need to be added as a selector for the event handler. Updated example:
https://live.datatables.net/sopifixu/2/edit

With this technique using something like cell().data() to get the index value probably won't work. Instead you will need to use the same technique to calculate it.

That code doesn't work with DataTables 2.x.

There seems to be a small race condition. I updated the test case with your code and removed a bunch of the rows. I added console output to see what order is being returned by columns().nodes():
https://live.datatables.net/niminomi/1/edit

Compare the output when the Datatable is initialized versus soritng by Position. The indexes are out of order and the console output is the same as the original order.

I updated the test case with a setTimeout function with 0 delay. That seems to have solved the issue.
https://live.datatables.net/yogezite/1/edit

@allan might be able to explain the need for the setTimeout().

Kevin

kthorngren

Looks like Allan and I cross posted

Kevin

allan

On the plus side, we both said basically the same thing, so it must be right .

The reason for the setTimeout in your last example is that the event happens too early for the API to see the updated data order / filter. Using draw I think would be the way to go for this. Worth noting that it is also possible to use on now so you don't need to trigger an extra draw to get the data to update on the first pass: https://live.datatables.net/yogezite/2/edit .

Allan

bg7

Allan and Kevin,

Thanks a ton for the explanations and examples. Allan - I dropped in your last example and it seems to be working well. I've defaulted the table to 500 records at a time and there's no noticeable lag. Previously with 10k records I'd see it lock up for up to 5-10 seconds at a time on the initial keystroke in the search box.

Would it be worth updating the DataTables index example page with this code either as a replacement or as a possible other option? I might not be the only one who runs into this.

https://datatables.net/examples/api/counter_columns.html

The bit about the timeout in the earlier examples above caught my attention. When I was initially experimenting with getting this to work I threw together something I suspect might be similar only using debounce:

    dt.on('order search', _.debounce(() => {
        dt.cells(null, dt.column('index:name').index(), { search: 'applied', order: 'applied' })
          .flatten().each(function (value, index) {
            this.cell(value).data(index + 1);
        });
    }, 10));

It worked but couldn't handle 10k records of course. I don't know if this is related but we've also been seeing some very odd behavior where rows are getting duplicated. We have a table that's polling for updates every two seconds. I think that the index column code was somehow getting caught up in a race condition and the row with the old value and another row with the new value (both with the same row id) were appearing. And the more rows the table had the more likely it was to happen (never saw it with a few hundred rows, saw it fairly often with 10k records). Very odd. Once I removed the old index code that behavior seem to disappear and I've not seen it since I updated it with the code you suggested using. I need to roll your updates out to one of our users who has a knack for making this (and other weird edge cases) magically appear and see if it's really gone.

Thanks.

Ben

allan

Agreed - I've updated the example.

Allan