Extremely LARGE Table Datasets

wdwyche

I am working with tables that have huge amounts of data (currently over a half million rows) with projected growth in the terabyte range once this kicks in gear finally.

I'm currently working with a considerably lesser dataset (just under 22K) to get this working. This data is loaded in a dataTable (obviously). The times to load are horrible. I've tried "HTML" (i use server side modperl scripting to dynamically generate everything) as well as "server side" using JSON encoded data:


print " \$('#vpewsreport').dataTable( {\n";
print " \"bProcessing\": true,\n";
print " \"bServerSide\": true,\n";
print " \"sAjaxSource\": \"/Reporting?op=json\",\n";
print " } );\n";

and an excerpt from /Reporting?op=json ...

print "{\n";
print " \"sEcho\": 1,\n";
print " \"iTotalRecords\": \"$recordCount\",\n";
print " \"iTotalDisplayRecords\": \"$recordCount\",\n";
print " \"aaData\": [\n";

while (my $record = $record_set->each) {
if ( $recordCount == 1 ) {
print " [ \"$record->[4]\", \"$record->[2]\", \"$record->[1]\", \"$record->[3]\", \"$record->[0]\" ]\n";
} else {
print " [ \"$record->[4]\", \"$record->[2]\", \"$record->[1]\", \"$record->[3]\", \"$record->[0]\" ],\n";
}
$recordCount--;
}
print " ]}\n";

Both ways "eventually" load in FF and just flat times out in IE. However, we are talking like 2 mins, 30 seconds to load and render 21,862 rows.

I'm using version 1.9.1.

cjxmccac

You're going to have to go full server side processing and just load in the necessary rows at the time they are needed.

http://www.datatables.net/release-datatables/examples/data_sources/server_side.html

wdwyche

I thought I was using server side. This URL you posted was what I was going by and the only real thing it does different is the:

"bServerSide": true,
"sAjaxSource": "../server_side/scripts/server_processing.php"

initialization parameters. I was doing that and it was making the call to a modperl script. However, the examples page doesn't really talk about the individual XHR calls it makes...with each call passing in an incrementing sEcho value and about 20 other parameters.

Here is the better page that explains the server side processing:

http://datatables.net/usage/server-side

I checked my webserver access logs and found the string of params and that evidence drove it home. Since I already load all params into an array with name/value pairs, it was easy to modify the query I was already running and set the limits on the selected data using values I already had in the array.

Thanks for the "go full server side processing" comment. It got me to thinking that maybe there was more to it than what was in the table init.

wdwyche

Now, here is another problem I've run into with the large dataset tables and going to full server side mode where it fetches a subset of data with each click of "Next" and does a draw of rows. The problem is with the use of the TableTools extra where you enable Save to CSV/PDF buttons etc. It is saving ONLY the current rendered dataset (iDisplayLength variable quantity).

I thought the whole purpose of going serverside was to facilitate loading the table for huge datasets so it doesn't impact the user performance-wise.

Unless I'm doing something wrong or just haven't enabled an option, from an end user's perspective I'd think this was a bug since the table visually shows where you are in a large set of rows (Showing 261 to 280 of 21,862 entries for example) and when i save it, I'd expect to save 21,862 rows and not a mere 20.

Moreover, shouldn't there be a way that it (behind the scenes) fills the table with data while the user is digesting the first screen of data?

allan

Hi,

There are two points here to bare in mind:

1. You are using server-side processing, which means that DataTables only knows about the rows currently displayed. You provide information to it that there are other rows available (iDisplayRecords etc), but it doesn't know anything about them.

2. TableTools is client-side - intentionally so, to make installation as easy as possible.

With these two facts, you can see how TableTools will only export the rows that DataTables knows about on the client-side. To do a full export when using server-side processing, you will want to use the "download" plug-in for TableTools which will request the file from the server: http://datatables.net/extras/tabletools/plug-ins#download .

Allan