jqGrid Taking Long Time For Large Records

Question

I am using jgGrid. Its working perfectly but when i pass about 90,000 records and open the page in google chrome it takes around 8 sec to create a grid which in my case it should be near 3-4 sec. and moreover when i run the same page on IE it became unresponsive.

Any suggestion how can i reduce the time ?

my script

function GetCommodityGrid(array) {
 array = array.rows; // assign rows array to array object
totalRows = array.length;
    jQuery(document).ready(function () {
        jQuery("#list").jqGrid({
            datatype: 'local',
            data: array,
            colNames: ['COM_NAME', 'COM_CODE', 'DELV_UNITS', 'LOT_SIZE', 'TICK_SIZE', 'TICK_VALUE'],
            colModel: [
        { name: 'COM_NAME', index: 'COM_NAME', width: 90, editable: true },
        { name: 'COM_CODE', index: 'COM_CODE', width: 100, editable: true },
        { name: 'DELV_UNITS', index: 'DELV_UNITS', width: 80, align: "right", editable: true },
        { name: 'LOT_SIZE', index: 'LOT_SIZE', width: 80, align: "right", editable: true },
        { name: 'TICK_SIZE', index: 'TICK_SIZE', width: 80, align: "right", editable: true },
        { name: 'TICK_VALUE', index: 'TICK_VALUE', width: 150, sortable: false, editable: true }
    ],

            rowList: [50,100,200],
            rownumbers: true, // show the numbers on rows
            loadonce: true,
            pager: '#pager',
            sortname: 'COM_NAME',
            viewrecords: true, // show the total records on the end of the page
            editurl: "TestGrid/EditRecord",
            caption: "JSON Example",

            //new option

           gridview: true,
           autoencode: true,

        });


        $("#list").jqGrid("navGrid", "#pager", { add: false },
    { //the Edit options
        closeAfterEdit: true,
        afterSubmit: function (response) {
            // you should return from server OK in sucess, any other message on error
            alert("after Submit");
            if (response.responseText == "OKK") {
                alert("Update is succefully")
                return [true, "", ""]
            }
            else {
                alert("Update failed")
                $("#cData").click();
                return [false, "", ""]
            }
        }
    });



    });
}

probably there are some other callbacks (`loadComplete` or `gridCompleate` which you not posted here)? — Oleg, Aug 27 '14 at 12:09
@Oleg no i did not subscribe any load complete or gridcomplete event. — shujaat siddiqui, Aug 27 '14 at 12:40

score 4 · Accepted Answer · edited Oct 03 '14 at 08:18

I analysed process of loading local grid with 90,000 unsorted rows and have found very funny bottleneck which one can easy overcome. First of all here is the demo which works quickly and here is almost the same demo which works much slowly especially in IE.

The most time of loading jqGrid get the line of jqGrid code directly at the beggining:

var p = $.extend(true,{
    // there are here different default values of jqGrid parameters
}, $.jgrid.defaults, pin || {});

Because one uses true as the first parameter then jQuery makes full copy of the data and it works slowly (why? It's another pure jQuery question).

As a workaround I suggest to set no data parameter during creating the grid. In the case the default parameter data: [] will be used. Instead of that one can set data inside of onInitGrid callback:

$("#list").jqGrid({
    //data: gridData,
    datatype: "local",
    ....
    onInitGrid: function () {
        // get reference to parameters
        var p = $(this).jqGrid("getGridParam");

        // set data parameter
        p.data = gridData;
    }
});

As the result the performance of loading of the grid will become much better.

I will post later my suggestions to trirand how to make small change of the code of jqGrid to improve the performance of jqGrid in the case. What I suggest is very simple. One can save data parameter in a variable, then call var p = $.extend(true,{...}); and then set data parameter directly in p variable

    // save local data array in temporary variable and remove from input parameters
    // to improve performance
    var localData;
    if (pin != null && pin.data !== undefined) {
        localData = pin.data;
        pin.data = [];
    }
    var p = $.extend(true,{
        // there are here different default values of jqGrid parameters
    }, $.jgrid.defaults, pin || {});
    if (localData !== undefined) {
        p.data = localData;
    }

The demo uses the fixed code of jqGrid and it works quickly.

UPDATED: The pull request which I posted to trirand is already merged to the main code of jqGrid on github (see more in the bug report). So the next version of jqGrid (version higher as 4.6.0) won't have the described problem.

score 0 · Answer 2 · edited Aug 27 '14 at 12:19

Try loading JqGrid as on demand, i.e load smaller chunk of data at one time instead of all data and load remaining data on pagination.

Here is a sample code for reference

PHP with MySQL

...
$page = $_GET['page']; // get the requested page

$limit = $_GET['rows']; // get how many rows we want to have into the grid

$sidx = $_GET['sidx']; // get index row - i.e. user click to sort

$sord = $_GET['sord']; // get the direction

if(!$sidx) $sidx =1;

// connect to the database    
$db = mysql_connect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());    

mysql_select_db($database) or die("Error conecting to db.");

$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id");

$row = mysql_fetch_array($result,MYSQL_ASSOC);

$count = $row['count'];    

if( $count >0 ) {
    $total_pages = ceil($count/$limit);
} else {
    $total_pages = 0;    
}

if ($page > $total_pages) $page=$total_pages;

$start = $limit*$page - $limit; // do not put $limit*($page - 1)

if ($start<0) $start = 0;

$SQL = "SELECT a.id, a.invdate, b.name, a.amount,a.tax,a.total,a.note FROM invheader a, clients b WHERE a.client_id=b.client_id ORDER BY $sidx $sord LIMIT $start , $limit";

$result = mysql_query( $SQL ) or die("Couldnt execute query.".mysql_error());

$responce->page = $page;

$responce->total = $total_pages;

$responce->records = $count;

$i=0;

while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {

    $responce->rows[$i]=$responce->rows[$i]['cell']=array($row[id],$row[invdate],$row[name],$row[amount],$row[tax],$row[total],$row[note]);

    $i++;
} 

echo $json->encode($responce); // coment if php 5
//echo json_encode($responce);
...

but my requirement is to get all data at once. – shujaat siddiqui Aug 27 '14 at 12:41 — shujaat siddiqui, Aug 27 '14 at 12:41

jqGrid Taking Long Time For Large Records

2 Answers2

Linked