Switching to server-side code with JSON object

Minerva

The following code is for a datatable that displays fine. Unfortunately I have no way showing you this on a public server because I am displaying in Powerchart (a hospital electronic patient chart). The table displays a patient's lab results, and the user can specify how many hours they want to go back (numHours). The problem is that sometimes people have so many lab results that it takes forever to load, so I want to switch to server-side code and the Scroller. I'm not clear on how to do this with a JSON object. Do I need to scrap the following syntax altogether? Can you point me to an example that uses JSON and server-side processing? Thank you for any information or ideas.

[code]function FillLabTable (numHours) {

// XMLCclRequest is the Powerchart version of XMLHttpRequest
var LabInfo = new XMLCclRequest();

LabInfo.onreadystatechange = function () {
if (LabInfo.readyState == 4 && LabInfo.status == 200) {

// convert to JSON object:
var msgLab = LabInfo.responseText;
if (msgLab != undefined && msgLab != null) {
var jsonLab = eval('(' + msgLab + ')');
}

// do we have any data?
if (jsonLab && jsonLab.DATAREC.LIST && jsonLab.DATAREC.LIST.length > 0) {

// we have data. Draw datatable
$('#LabResultTable').html(' ');

var oTableLab = $('#labDataTable').dataTable({
"aoColumns": [
{ "sTitle": "Date", "sWidth": "40px" },
{ "sTitle": "Name", "sWidth": "200px"},
{ "sTitle": "ID", "bVisible": false }
],
"bAutoWidth": false,
"aaSorting": [[1, 'desc']],
"bSortClasses": false,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"iDisplayLength": 15,
"oLanguage": { "sLengthMenu": 'Display 5'+
'1015'+
'2030'+
'All records'
}
}); // var oTableLab =

if (oTableLab != undefined) {
oTableLab.fnClearTable();
};

for (var i = 0; i < jsonLab.DATAREC.LIST.length; i++) {

// DATE COLUMN:
var myDate = jsonLab.DATAREC.LIST[i].EVENT_DT_DISP
var labDate = "" + myDate + ""

// LAB RESULT COLUMN:
var docTitle = jsonLab.DATAREC.LIST[i].EVENT_TITLE_TEXT
var strURLbegin = "";
var strURLend = ""
var labResult = "" + strURLbegin + docTitle + strURLend + ""

oTableLab.fnAddData([
labDate,
labResult,
jsonLab.DATAREC.LIST[i].EVENT_ID // hidden
]);
} // for (var i = 0;

} // if (jsonLab &&
else {
$('#LabResultTable').html('No information found for previous ' + numHours + ' hours')
} // else

} // if (LabInfo.readyState
} // LabInfo.onreadystatechange

// Call the ccl progam and send the parameter string:
LabInfo.open("GET", "memil_mpage_review_lab");
LabInfo.send("^MINE^, value($VIS_Encntrid$), value($PAT_Personid$), ^" + numHours + "^");
return;
}; // END FillLabTable()[/code]

allan

Hi Minerva,

The easiest change I can see in your code above is to change your fnAddData call to:

[code]
oTableLab.fnAddData([
labDate,
labResult,
jsonLab.DATAREC.LIST[i].EVENT_ID // hidden
], false);
[/code]

And then add a call to oTableLab.fnDraw() at the end of the loop (outside the loop).

Currently it is redrawing the whole table every time a new row is added, which is going to be a huge(!) performance hit with a lot of rows.

If that isn't enough, then the next question is: how many rows of data are we talking about here?

If you want better performance than just the fnAddData change above, the path of least resistance to get a performance boost is to use Scroller, Ajax data loading and deferred rendering ( http://datatables.net/release-datatables/extras/Scroller/index.html ). What happens with this set up is that the whole data set is loaded initially (so there is still that hit, if the file is massive, or slow network), but it only renders rows in the table when needed - which can give a _huge_ performance boost - particularly in IE. I would say that this will work well for up to tens of thousands of rows.

The alternative is to use server-side processing, which will copy with many millions of rows (really the server DB is the limit there, but I've seen more than 20 million rows in a DataTable). This would involve a server-side script being created to do the filtering, sorting and paging (there are a number of pre-built scripts here: http://datatables.net/development/server-side/ .

So, for the first port of call I would suggest the fnAddData change and see how that goes for you.

If you want to take it a step further, than I would suggest the next step is to rewrite the code above to use DataTables' Ajax get, rather than your own, since that will make switching to Scroller (for either client-side or server-side processing) much easier! Let me know if you want to go that route and I'll look at rewriting the code about to do what is needed with the DataTables options :-)

Regards,
Allan

Minerva

Wow, Allan. That makes a big difference. From 19 seconds to 3 seconds to load the page. I'm going to call this one good for now. A patient with A LOT of labs is still only 100-200 rows - not mega amounts of data. Thank you for looking at my code.

allan

Great to hear that makes a good difference. I would suggest that to polish off this implementation for the moment then, enable deferred rendering ( bDeferRender ). Since the majority of the rows won't be visible in the first instance, it would save a bit of time from creating the elements when they are added to the table, and will then only create then when needed for the display. Will hopefully shave off another second or so :-).

Regards,
Allan

Minerva

Thanks! Will do.