Tablesorter choking with large data sets?

[ggongon]

I am working with an API that is returning up to 3000+ rows in extreme cases. When building a table with this data (14 columns), the table gets rendered fine but when i apply tablesorter with pagination, Firefox goes unresponsive as it cannot handle the load.

I am forced to limit my table display to 500 rows max, at which point the browser goes into Not Responding state for about 2 seconds before rendering. Is this a limitation of Tablesorter when handling larger data sets?

I am using tablesorter 2.7.5, jquery 1.8.3, and firefox 19.0.2.

[Mottie]

I tried to optimize Tablesorter as best I could. If you check out this demo which has a table of 1023 rows and 7 columns, it takes around 0.2 seconds to do the alphanumeric sort and 0.12 seconds for a numeric sort, in Chrome. It was surprising to me that Firefox 19.0.2 does it in about half of that time, and not so surprising that IE10 needs double the time. I put together a table of about 3000 rows and Firefox was still preforming very well.

There are a few things that would decrease performance that you might want to check:

1. Inefficient custom parsers. Share the code and I can try to help optimize it.
2. Inefficient custom textExtration functions - node.textContent is much more efficient than other methods, for pretty much all versions of IE (ref)
3. Widgets that need to apply styles to one or more cells in every row (i.e. the columns widget) - don't use it!
4. Widgets that need to apply styles to every row (i.e. zebra widget)
5. Widgets that need to search the table DOM for text/HTML (i.e. filter widget; but you can make it search through the parsed data, which is much faster)
6. Age of the browser (IE7 & 8 will be slow, period).

I'm sure there are more, but I can't think of them right now.

So, if none of that works you have two options:

1. Decrease the amount of data you show at once. In my table with 3000 rows, the file size was around 330 Kb. I can tell you right now that your mobile users won't like that and if it was me, I would find the information I need elsewhere.
2. Try some other table sorting plugins. I'm just one guy. There are a few other plugins out there with scores of programmers nit-picking, optimizing and more knowledgeable about optimization than me. See if their plugin can do it faster (e.g. try Datatables (especially this demo) or jqGrid).

[ggongon]

Thanks for the quick response! Your suggestions were spot on. I did realize i forgot to mention that I was using both the columns and zebra widgets, which was contributing to the problem. I'm also using pagination, which may lend a hand, but i'm doubtful that Pager is causing a significant performance hit.

In further digging, i noticed that we had at some point introduced a custom textExtraction method (perhaps when using v2.4.1 ), which looks to be causing a significant performance hit. Here's the code sample:

textExtraction: function(node) {
    noded = node.childNodes[0];
    if($(noded).is("select")) {
        return node.childNodes[0].childNodes[0].innerHTML;
    } else if($(noded).is("input")) {
        return node.childNodes[0].value;
    } else {
        return node.innerHTML;
    }
},  

Thanks again!

[Mottie]

I'm glad you found the problem! :)

Just so you know, the zebra widget isn't nearly as bad as the columns widget, although you may still notice a difference between using and not using it. Either way it might be better to just use css row styling. Something like this:

.tablesorter tbody > tr:nth-child(odd) > td,
.tablesorter tbody > tr:nth-child(odd) > th {
    background-color: #f9f9f9;
}

Also, try using the new input-select parser. It uses delegation to automatically update the inputs/selects if the user changes them. The major difference from this parser from the other default parsers is that you'll need to set a column (via classname or jQuery data) to use it, it won't automatically detect the column.

Edit: Oh and I added textExtraction to my list above for prosperity :)

[Alchaves]

I am having the same problem here. My API is returning 4400+ rows with 9 columns (it's all the employees of the company, with only the basic information: nr, name, job, location...) and it takes alot of time (it takes more than 30seconds to load everything up) to Firefox to render it (not to metion that it goes unresponsive for most of that time).

The zebra effect is done by css and pretty much every widget is turn off, with the exception of the filter and pager (with a table of this size we have to use those).

We've run out of ideias to make it faster...

Do you have any suggestions?

[Mottie]

I sometimes test tablesorter on a table with 4000+ rows and 7 columns, and I definitely can see the lag you mention. The file itself is over 700 KB in size and makes it very inefficient, especially for mobile use.

The pager does include a removeRows option which when true will only maintains the currently viewed rows within the table, but the filter widget doesn't support this "mode" - I will look into fixing this soon.

Ultimately, I think the best solution would be to serve up the row data from a database.

[thezoggy]

load up chrome dev tools timeline/profile and see what is taking so long.. is it the http requests to generate the page? is it ajax calls? is it web cache not being utilize correctly? is it gzip not being enabled on the server? is it tablesorter initializing? page reflow due to css/js? is it a specific tablesorter parser/function your using? etc

https://developers.google.com/speed/articles/reflow http://addyosmani.com/blog/performance-optimisation-with-timeline-profiles/

https://developers.google.com/chrome-developer-tools/docs/timeline http://code.tutsplus.com/tutorials/chrome-dev-tools-javascript-and-performance--net-29671

[ggongon]

like Mottie says, the best solution is definitely to serve up a subset of the data and just make subsequent calls while passing in a page #.

The API i had to work with did not support pagination, and they refused to add this flexibility. So, i ended up building my own solution, which was to store the raw response data locally and provide my own pagination. then used tablesorter to pretty up only the subset of visible table rows.

[mohitmayank]

Guys. I face this issue myself. I am using tablesorter extensively at my work. most of my tables are small and run fine. but few tables where i have 5000+ rows. I cant use tablesorter's paging cause i need the filter ability. I also do aggregations based on filters. My API (REST) is custom and supports search query as well as pagination. But forking tablesorter to use APIs filter/pagination at least for me is a big exercise and I haven't been able to find time for that. And I need to display that 5k lines of data.

@Mottie - You were working on version 3, right? can we have the code refactored to make it more modular. Its been long i last saw the code. But if you are willing to consider this, i'll try reading it again and suggest something.

For example if i know before hand which type of data will be there in which column, insted of running auto-parsers, can i provide this information to TS. Also i build my tables on the fly. Adding the rows to table after they are received from API and i have a collection of parsed data in my code (I am using backbone) can i use that somehow.

[thezoggy]

I've personally used tablesorter with several thousand rows and its super quick.

Obviously different things come into play.. browser? browsers css/js handling? whats the specs of the machine you are running and what is 'slow' to you.

[Mottie]

@mohitmayank Well, my time is somewhat limited, so I really haven't done much with version 3. And, yes I want to make it modular - the core plugin will just be methods to add, remove, apply and refresh widgets, with some core essentials. Everything else will be a widget (or maybe calling them modules would be better), including sorting. Once I figure out how to use grunt and jekyll I'll get up a build process as well. Ugh, and then update all the documentation, because I want to streamline the options as well.

I think I can make the filter widget work with the cached rows instead of the actual rows within the table, so then the pager can be set to only show the minimum number of rows.

I hope to push out the next update very soon, but I keep getting distracted with other fixes, updates and life in general. I'll save this filter widget modification for a later update (but not that much later, if I can work out all the problems).

[mohitmayank]

@thezoggy true, there are a lot of things to consider for the slowness. I dont have any zibra, column widget. no css/jquery/js manipulation for rows. I have a onclick event applied on whole table. i am using latest chrome on i7 machine with 8 GB ram. so i dont think specs are low.

I insert 100 rows at a time after they come from server, typically 10 ms. I let the use decide when to apply tablesorter. before the user has applied TS, there is no lag. but when TS is enabled and row count goes beyond few thousand, there is a lag whenever the table is filtered.

i do use jquery to find the count of rows post filter. $('tr.tableDataRow:not(.filtered)').size();

once that is done i also run a method to find sum/avg of sum columns.

self.$('tr.tableDataRow:not(.filtered)').each(function(){
     that.footer.lineRunner.call(self, memory, self.rows[$(this).attr('id')]);
}); 
//lineRunner uses the dataSet i have separate from TS. 
//the same dataSet used for rendering the TS in the first place

i am ok if count and sub/avg takes time. But i dont want browser to hang/block ui when the filter is working its magic. My every method post/pre rendering/filter runs defered using setTimeout or underscore/lodash _.defer method. But i still see a seconds unresponsiveness after the filter. for filter's live search i use this config

filter_liveSearch : true,
filter_searchDelay : 800,

[mohitmayank]

@Mottie i never used pager or any other widget beside filter, stickyHeaders, uitheme and resizable. And you hardly ever receive issues on them. :P. i am on a very tight schedule myself. But i'll try to do what i can.

[thezoggy]

@mohitmayank using advanced selectors like :not on multiple class instances prob could be optimized.. maybe run some jsperf to figure out the best way. then doing a .each function for each one.. i can see this being slow/badly optimal design. if you drop that method to do your sum does your tablesorter 'lag' go away? I have a feeling your wrongly identifying the source of your problems.

[mohitmayank]

@thezoggy i run them on 'filterEnd' event. and i use _.defer (underscore's replacement of setTimeout(fun, 0); ).

And i am getting lag just after i type in a filter. the time between 'filterStart' and 'filterEnd' or 'sortStart' and 'sortEnd' is problem. i use timer combination to insure I postpone aggregation and count while tablesorter is between filterStart-filterEnd or sortStart-sortEnd

[Alchaves]

First of all thanks for your quick reply and sorry it took me so long to reply, but I've been experiencing some issues with our database that kept me from taking another look at this issue.

Our intent it's to keep using the filter and pager widget, since we are handling a great amount of data (in our point of view, obviously). We've been able to improve the query and it only speed everything up by a couple of seconds...

I'll return to this issue as soon as possible.

[Alchaves]

Hi again. I finally had some time to look at this issue again, and found out that part of the problem is due to our javascript that is causing some delay. We'll keep working on it, to see if we can reduce the amount of time that the browser spends on our javascript, while we seek others ways of improve that particullar page... Thanks to all for your suggestions!

[sidazad]

I believe the problem is happening because of the code on line 1119 of jquery.tablesorter.js.

if (type === 'mouseup' && external !== true && (tmptime - downTime > 250))

Since for a large table time is consumed after the mouseup event, tablesorter thinks that the user is trying to resize the column (as opposed to sorting it) and hence doesn't trigger a sort. I was able to get around it by increasing this delay. Although this is a hack, we are not using resizable columns so it works for us.

[racin]

Hi, I had a similar issue with Internet Explorer 8/9/10. I was able to reduce the processing time by up to 95%.

My fix was to modify the following code in jquery.tablesorter.js

            ts.processTbody = function(table, $tb, getIt){
                table = $(table)[0];
                if (getIt) {
                    table.isProcessing = true;
                    return $tb;
                }
                table.isProcessing = false;
            };

The function is found around line 1235

[crazyserb]

I find it interesting that with the latest version 2.22.1 the performance is quite an issue on Firefox (with zebra, filter, ui-theme widgets enabled) and it takes about 10 seconds to fully load a dataset of about 80 records on a page (sure, there are about 45 columns, but that shouldn't be that much work)... whereas on Chrome that takes about 2-3 seconds in total, and the browser doesn't choke and doesn't come up with JS processing errors like FF does.

Any ideas how to improve things on FF at all?

I've tried the above two suggestions and the 1st one I couldn't find at all in the code and 2nd one didn't do much, if anything.

[crazyserb]

I've also tried implementing these (ajax loading of extra data):

https://mottie.github.io/tablesorter/docs/example-pager-ajax.html https://mottie.github.io/tablesorter/docs/example-widget-pager-ajax.html

but the sorting or filtering on those examples alone isn't working at all, so if I add the ajax loading functionality into 400 record table I'm working with I might cut down the loading time by quite a bit, but am I then giving up the sorting and filtering for that?

Ugh...

[Mottie]

@crazyserb Try using the pager without ajax first and see if that helps with the loading time.

You're not really giving up sorting & filtering, you're just shifting it to the server instead of the client. The client can't sort or filter the entire data set because it doesn't have access to the entire data set.

[crazyserb]

Ahh... so if I go the ajax route, the client can only sort/filter through the data set that they've loaded. Hmm, ok, there goes that idea then.

I'm already using the pager and it takes a loooong time to load 300 records with 50 columns + x-editable attached to a lot of them... I have it limited to show 25 records per page by default, but it still takes a while to load everything first and then filter out the first 25. If I limit the SQL query to 25 records only, then it takes 2-3 seconds tops to load everything. With full 300 records set it takes about 30-40 seconds.

On Thu, Sep 3, 2015 at 7:57 AM, Rob G [email protected] wrote:

@crazyserb https://github.com/crazyserb Try using the pager without ajax first and see if that helps with the loading time.

You're not really giving up sorting & filtering, you're just shifting it to the server instead of the client. The client can't sort or filter the entire data set because it doesn't have access to the entire data set.

— Reply to this email directly or view it on GitHub https://github.com/Mottie/tablesorter/issues/273#issuecomment-137416643.

[Mottie]

Ouch, why 50 columns? Can't you break that up into smaller chunks?

Maybe I should make the pager page the columns as well...

[crazyserb]

Client requirements, unfortunately.

And even if I add the Bootstrap "Select Columns" button/option for the client to select which columns they want to show/hide on load, the full 50 columns are still being loaded initially and then are being filtered down to select few. Can that be cached somehow or... something?

On Thu, Sep 3, 2015 at 9:39 AM, Rob G [email protected] wrote:

Ouch, why 50 columns? Can't you break that up into smaller chunks?

Maybe I should make the pager page the columns as well...

— Reply to this email directly or view it on GitHub https://github.com/Mottie/tablesorter/issues/273#issuecomment-137449029.

[Mottie]

What is causing the biggest delay? Can you initialize an empty table, then slowly fill it so the user doesn't experience a blank page?

Also, have you tried the editable widget? I don't know if x-editable might be contributing to that long initialization time.

[crazyserb]

I'm playing around with loading times, pieces that are being called and loaded to figure out what's bringing it to a crawl...

And I've looked at the editable widget (you're referring to this? https://mottie.github.io/tablesorter/docs/example-widget-editable.html) but it was very rudimentary and basic, and didn't offer most of the features I found available in x-editable: http://vitalets.github.io/x-editable/demo.html

On Thu, Sep 3, 2015 at 9:46 AM, Rob G [email protected] wrote:

What is causing the biggest delay? Can you initialize an empty table, then slowly fill it so the user doesn't experience a blank page?

Also, have you tried the editable widget? I don't know if x-editable might be contributing to that long initialization time.

— Reply to this email directly or view it on GitHub https://github.com/Mottie/tablesorter/issues/273#issuecomment-137451922.

[ctournayre]

Hi here, I resurrect an old topic, but same here about 1000 lines, 9 columns. Take ~11sec to render in firefox while about 1sec in chromium (using latest version of tablesorter). I use widgest "zerba", "filter", "pager", but it doesn't change anything if I don't use them. I think it's a js rendering problem, still no idea ?

Thanks

[ctournayre]

Nevermind if found that it's a bug caused by AdBlock Plus, for those who experience the same problem, first try to launch your browser in safe mode (all module disabled) to test if it's the problem. I have to fully disable ABP to make it work at a descent speed, adding my website to exclusion (even when test in localhost) doesn't work.

[Mottie]

Hi @ctournayre!

Thanks for sharing! And sorry I didn't respond sooner.

[ayoubde]

Hi there, when I load data that has more than 15 rows with parent/child row I loose page size and I get error message saying: Uncaught TypeError: Cannot read property 'length' of undefined