Index column slows down table with large number of rows
Index column slows down table with large number of rows

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
This question has an accepted answers - jump to answer
Answers
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). Usedt.column('index:name', {page: 'current').nodes()
to get the cells to update andpage.info()
to make sure you start the index at the correct number for the current page!Regards,
Allan
One option is to use
selector-modifier
ofpage: "current"
socells().every()
only iterates the current page. This will require the use ofpage.info
to calculate the starting index for the page. Also thepage
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.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
Looks like Allan and I cross posted
Kevin
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. Usingdraw
I think would be the way to go for this. Worth noting that it is also possible to useon
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
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:
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