Using jquery datatable for the json response

Question

The json output that I have is below

[{"param1":"value1","param2":"value2","param3":"value3"},{"param1":"value1","param2":"value2","param3":"value3"}]

My ajax request is coded in a function so that it can called based on button click:

function callAjaxRequest()
{
ajaxRequest = $.get('getdata',{ 'parameter1': paramValue1,'parameter2':paramValue2 },function(responseinjson) {

if(responseinjson!=null){
        $("#table1").find("tr:gt(0)").remove();
        var tablevar = $("#table1");
        $.each(responseinjson, function(key,value) {

             var rowNew = $("<tr><td><td></td></tr>");
                rowNew.children().eq(0).text(value['param1']); 
                rowNew.children().eq(1).text(value['param2']); 
                rowNew.appendTo(tablevar);
        });

        var numberofrecords = responseinjson.length;

        }

}
}

I initialized ajax request to variable because I can abort it later in some cases.

Now my problem is I can see lot of delay in the for each loop based on the huge number of records. So, can someone please suggest me how to implement pagination according to the responseinjson value using datatable in jquery. I have seen lot of examples on the internet but I can't get my head around it on how to use according to the response that I get. Please go easy on my question if it sounds noob as I am new to web development.

P.S: I don't want to add to datatable using for each loop because it results in same delay that I am getting now.

Reiterating my point for bounty: I am getting entire json response in the ajax call as per my code snippet. But I found that it is taking considerable delay to show up the data, so the for each jquery loop to show data is taking too much time. So, is there anyway that I can paginate this without lopping through the entire json response.

Answer 1

Datatables has an in-built pagination feature, but using a server-side data source means that you can handle the paging in the server code.

Datatable initialisation:

var oTable = $('#mytable').dataTable({
    'bServerSide': true,
    'iDisplayLength': 25,
    'iDeferLoading': 0,
    'sAjaxSource': 'your-url-that-returns-json',
    'bProcessing': true,
    'aoColumns': [
        {
            //...
        }
    ]
});

Note the use of iDeferLoading which prevents initial population on page load; use .fnDraw(); so as to load on a button click.

When you make the request, parameters relating to paging, searching, sorting, etc., the corresponding values are also passed in into the query string and made accessible for use when querying the data.

For example in C#:

public ActionResult PopulateMyTable(jQueryDataTableParamModel param)
{
    var mydata = query the database
    var results = mydata.Skip(param.iDisplayStart).Take(param.iDisplayLength);
    // ...
}

jQueryDataTableParamModel is just a class to contain all the datatable parameters.

iDisplayStart & iDisplayLength are parameters passed in the request to populate the datatable, and used to return the paged data.

Then, return the json data:

return Json(new {
    param.sEcho,
    iTotalRecords = rowCount,
    iTotalDisplayRecords = rowCount,
    aaData = results
    }, JsonRequestBehavior.AllowGet
);

Answer 2

Refer to the following code, for Ajax and Datatable

$(document).ready(function() {
    $('#example').dataTable( {
        "processing": true,
        "serverSide": true,
        "ajax":{ 
               "url" : "../server_side/scripts/server_processing.php",
               "data":{
                       //your variable
                       }
               }
    } );
} );

Refer to the link here Datatable

Answer 3

You can do something like this (syntax depends on the plugin version too!):

1) Declare a function which manages your requests (check the syntax, I use custom utilities and this is not exactly what I have in my code), it's reausable: var fnServerData = function (sSource, aoData, fnCallback, oSettings) { var jqXHR = $.ajax({ dataType : 'json', url : sSource, data : aoData, success : fnCallback }); oSettings.jqXHR = jqXHR; return jqXHR; };

2) Reference it in your datatable declaration, example:

$('#example').dataTable({ "bPaginate": true, "bServerSide": true, "iDisplayLength": 10, "iDisplayStart": 0, "sAjaxSource": 'yoursource', "fnServerData": fnServerData });

Answer 4

If you're having issues with the amount of time this is taking to build the DOM elements I'd suggest looking at the performance implications of the way you are building row and cell elements.

Have a look at this JSFiddle http://jsfiddle.net/e4oc2jbj/ . On my browser using the jQuery based code you've got takes 200 ms to add 1000 rows, where as using native DOM method takes around 7ms

var newRow = table.insertRow(0);
var newCell = newRow.insertCell(0);
newCell.appendChild(document.createTextNode(value['param1']));
newCell.appendChild(document.createTextNode(value['param2']));

Maybe with better performance you wont need to page the data?

Answer 5

You can include this pagination plugin to achieve pagination in datatables:

http://www.datatables.net/plug-ins/pagination/

Answer 6

Besides create DOM elements as @Sam Greendhalgh mentioned, considere create a table "in-memory" instead of taking the one from the DOM and append rows to it.

I have tested your code and I can see the problem could be that you'r append elements directly to the DOM.

i.e.:

$("#myTable").find("tr:gt(0)").remove();
    var tablevar = $("#myTable");               
    for(var i=1;i<=100;i++){
        var rowNew = $("<tr><td><td></td></tr>");
        rowNew.children().eq(0).text("hola"); 
        rowNew.children().eq(1).text("hola"); 
        rowNew.appendTo(tablevar);
     }

The above code will create/render and append the rows directly to the DOM, that's very costly in performance for any browser and we're talking about 14k rows in your case.

So besides the recommendations about native methods to create the rows, you should consider (it might help, not sure) create the whole table in a previous element created in memory (see this answer: How to use createElement to create a new table? or search for others here in StackOverflow.)

then append the whole table to the DOM, and just render/show the rows according to your page size instead of showing the whole table.

Another thing it could help you current code, is set the table to hidden (it might help, again not sure): var tablevar = $("#myTable").hide();

Hope my answer help you a bit, but if it doesn't you can always to avoid invent the wheel: http://www.datatables.net/

And about:

is there anyway that I can paginate this without lopping through the entire json response.

Try with JSON.parse you coul try somethign like:

 var myjsobj = JSON.parse('[{"param1":"value111","param2":"value222","param3":"value333"},{"param1":"value1","param2":"value2","param3":"value3"}]');

 //now you could acess to your object using an index
 console.log(myJsObj[0]);  //so "just" left to implement the pagination logic

You would need to add an index for it in the json object so you can keep of the current position

Answer 7

my approach would be to go through the loop and call everything inside your loop in

document.setTimeout(function(){}, 0)

this way everything is pushed into the Queue and wont be blocking your interactions on the UI because your Render Queue can still push its rendering inbetween your loops..

basicly it doesnt spam your stack and just works it off when there's time for it.

I Hope you consider my answer. It worked for me aswell in a simular task ;)