Converting to Serverside processing
Converting to Serverside processing
Background:
I am using dT on a mysql database with a possible 10,000 rows returned from the sql.
jsp page with java method calls to java files.
The jsp starts by calling the java to get the data in an arrayList of objects.
For the table data, I am looping through the data with an iterator to build the table on the fly. The columns displayed can change in order and number. Some of the data is numerical codes converted via java calls to its descriptive equivalent. DataTables is running fine, doing what I ask it to.
The page loads too slow, it times out. The data relates to tasks that need to be completed. The default sort is based upon time received. I have the sql ORDER BY the time received column, then LIMIT 250. Since no one is assigned more than 30 tasks at a time, this seemed reasonable. This allows the page to load just fine.
The problem:
When someone sorts the data using the column headers, they are getting only the same 250 rows sorted, not necessarily the 250 highest matching the sorting criteria. I need to sort based upon potentially all 10,000 rows. Server side processing seems to be the way to go.
The Question:
I've started the conversion. How does my preloading of the data via Java calls interact with what datatables wants? Should I skip that and let datatables control all the data collection? The page seems to load the table (java) and then empty it(datatables?).
I am using dT on a mysql database with a possible 10,000 rows returned from the sql.
jsp page with java method calls to java files.
The jsp starts by calling the java to get the data in an arrayList of objects.
For the table data, I am looping through the data with an iterator to build the table on the fly. The columns displayed can change in order and number. Some of the data is numerical codes converted via java calls to its descriptive equivalent. DataTables is running fine, doing what I ask it to.
The page loads too slow, it times out. The data relates to tasks that need to be completed. The default sort is based upon time received. I have the sql ORDER BY the time received column, then LIMIT 250. Since no one is assigned more than 30 tasks at a time, this seemed reasonable. This allows the page to load just fine.
The problem:
When someone sorts the data using the column headers, they are getting only the same 250 rows sorted, not necessarily the 250 highest matching the sorting criteria. I need to sort based upon potentially all 10,000 rows. Server side processing seems to be the way to go.
The Question:
I've started the conversion. How does my preloading of the data via Java calls interact with what datatables wants? Should I skip that and let datatables control all the data collection? The page seems to load the table (java) and then empty it(datatables?).
This discussion has been closed.
Replies
[code] jQuery(document).ready( function () {
var oTable = jQuery('#assignment_table').dataTable({
//processing is done server side
"bServerSide": true,
//ajax page location
"sAjaxSource": "/admin/ajax.jsp?action=assignListSort",
"bProcessing": true,
"oLanguage": {
"sZeroRecords": "No results"
},
"fnDrawCallback": function ( oSettings ) {
/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
for ( var i=0, iLen=oSettings.aiDisplay.length ; i, "<%=sortDirection%>" ]],
"aoColumnDefs": [{
"bSortable": false, "aTargets": [0,1,2,3,4]
}],
"fnServerData": function (sSource, aoData, fnCallback) {
aoData.push({"name": "status", "value": "<%=plStatus%>" });
aoData.push({"name": "cabinet", "value": "<%=plCabinet%>" });
aoData.push({"name": "upload", "value": "<%=plUpload%>" });
aoData.push({"name": "analyst", "value": "<%=plAnalyst%>" });
aoData.push({"name": "column", "value": "<%=plColumn%>" });
aoData.push({"name": "operator", "value": "<%=plOperator%>" });
aoData.push({"name": "criteria", "value": "<%=plCriteria%>" });
aoData.push({"name": "includeAll", "value": "<%=plInclude%>" });
aoData.push({"name": "isAnalyst", "value": "<%=isAnalyst%>" });
aoData.push({"name": "userId", "value": "<%=userIdi%>" });
aoData.push({"name": "userDeptId", "value": "<%=userDeptIdi%>" });
jQuery.ajax({
"dataType": "json",
"type": "POST",
"url": sSource,
"data": aoData,
error: function (jqXHR, textStatus, errorThrown) {
ajaxFailedFunctionToCall = null
//jQuery("#assignment_table_processing").css({visibility: "hidden"});;
alert(jqXHR.status+','+textStatus+','+errorThrown);
},
success: function(data) {
if(data.exportLocation != undefined) {
window.location = data.exportLocation;
}
fnCallback(data);
}
})
}
});
new FixedHeader( oTable, { "zTop": "10" });
});
[/code]
sEcho=1&iColumns=26&sColumns=&iDisplayStart=0&iDisplayLength=10&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&sSearch_5=&bRegex_5=false&bSearchable_5=true&sSearch_6=&bRegex_6=false&bSearchable_6=true&sSearch_7=&bRegex_7=false&bSearchable_7=true&sSearch_8=&bRegex_8=false&bSearchable_8=true&sSearch_9=&bRegex_9=false&bSearchable_9=true&sSearch_10=&bRegex_10=false&bSearchable_10=true&sSearch_11=&bRegex_11=false&bSearchable_11=true&sSearch_12=&bRegex_12=false&bSearchable_12=true&sSearch_13=&bRegex_13=false&bSearchable_13=true&sSearch_14=&bRegex_14=false&bSearchable_14=true&sSearch_15=&bRegex_15=false&bSearchable_15=true&sSearch_16=&bRegex_16=false&bSearchable_16=true&sSearch_17=&bRegex_17=false&bSearchable_17=true&sSearch_18=&bRegex_18=false&bSearchable_18=true&sSearch_19=&bRegex_19=false&bSearchable_19=true&sSearch_20=&bRegex_20=false&bSearchable_20=true&sSearch_21=&bRegex_21=false&bSearchable_21=true&sSearch_22=&bRegex_22=false&bSearchable_22=true&sSearch_23=&bRegex_23=false&bSearchable_23=true&sSearch_24=&bRegex_24=false&bSearchable_24=true&sSearch_25=&bRegex_25=false&bSearchable_25=true&iSortingCols=1&iSortCol_0=6&sSortDir_0=desc&bSortable_0=false&bSortable_1=false&bSortable_2=false&bSortable_3=false&bSortable_4=false&bSortable_5=true&bSortable_6=true&bSortable_7=true&bSortable_8=true&bSortable_9=true&bSortable_10=true&bSortable_11=true&bSortable_12=true&bSortable_13=true&bSortable_14=true&bSortable_15=true&bSortable_16=true&bSortable_17=true&bSortable_18=true&bSortable_19=true&bSortable_20=true&bSortable_21=true&bSortable_22=true&bSortable_23=true&bSortable_24=true&bSortable_25=true&status=open&cabinet=0&upload=false&analyst=535&column=claim_number&operator=eq&criteria=&includeAll=false&isAnalyst=true&userId=16339&userDeptId=51
I think this looks pretty good. But my json is coming back empty. The data in aoData.push is getting to my java. Suggestions on what I should check for?
d[b] is undefined
Allan
JSON
{ "sEcho":"1",
"iTotalRecords":0,
"iTotalDisplayRecords":4,
"aaData": [[],[],[],[]] }
When I call the database, I use the following for my query LIMIT:
iDisplayStart +","+ (iDisplayLength*3 + iDisplayStart);
using just iDisplayStart, IDisplayLength was giving me issues with the paging buttons. I would get a JSON error when I tried to load the page number that I was mulitplying by. Adding iDisplay start has solved that. But the LAST button in paging ignores iTotalRecords and seems to base itself upon the filtered rows returned to the table.
Can I capture the action of clicking on the LAST button so I can change the values used in my LIMIT?