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, "", ""]
}
}
});
});
}
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.
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);
...
