Extremely LARGE Table Datasets
Extremely LARGE Table Datasets
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.
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.
This discussion has been closed.
Replies
http://www.datatables.net/release-datatables/examples/data_sources/server_side.html
"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.
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?
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