Assigning Data Source Batch By Batch in JQWidgets JQX Grid
Introduction
If you are new to the term JQX Grid then please read here: JQX Grid With Filtering And Sorting. For the past days I have been working on JQX Grid. Now I will share a tip with you that you may find useful when you work with a large amount of data. It might be useful when you work with the Dashboards also.
Download the necessary files here.
Background
When you work with a large data set or large data source, you may find many performance issues. You may need to spend hours improving the performance, that is also for saving a few seconds. The problem that I encountered is different, since my data source is ADOMD cell set, I wanted to convert the cell set to HTML table and from there to JSON and JSON Array. I could do that part successfully. I had a grid that has around 20k records (I know it is a less amount of data). But still it was taking 1 minute to load fully in my client’s production environment . Yeah, that was too low. So I decided to go with the server-side paging as I could find a demo in JQWidgets blogs. But the real problem is , the Query formation is really tough in ADOMD since my measures and dimensions are not static, everything is dynamic in my dashboard application. So I encountered many problems. Later I found server-side paging is not possible in my application, hence I decided to load the data as a batch. Let’s say we will load the first thousand the first time. So the grid will load in a blink. And of course, my client will fall happily 🙂 And in the background, I will be creating an array by 1000 by 1000 . Once it reaches the maximum length (in other words all the record that we need to bind to the grid), I will assign the data source to the grid. Since this entire process is handled in the background, no one knows about this, except you and me 😉 . So let’s move into the code.
Download the needed files here
Using the Code
I hope you have downloaded the necessry files, if not then please download it.
What we need
Simple HTML
[html]
<!DOCTYPE html>
<html lang=“en”>
<head>
<title id=‘Description’>Load JQX Data batch by Batch – Sibeesh|Passion
</title>
</head>
<body class=‘default’>
<div id=“jqxgrid”></div>
</body>
</html>
[/html]
Include References
[js]
<script src=“jquery-1.9.1.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxcore.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdata.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxbuttons.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxscrollbar.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxlistbox.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdropdownlist.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.filter.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.sort.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.selection.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.pager.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.columnsresize.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.columnsreorder.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.export.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdata.export.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdatatable.js”></script>
<script src=“JQXItems/jqwidgets/jqxmenu.js”></script>
<link href=“JQXItems/jqwidgets/styles/jqx.base.css” rel=“stylesheet” />
[/js]
So now we will formulate a JQX grid in the div that has id as jqxgrid. To begin, we need some data to bind to the grid right. As of now we will create an Array of elements in the client side itself. Of Course we will be using jQuery for the array formulation. You can get the data from your database or from a local file if you want. You may need to use some ajax call in jQuery for that.
Create Dummy Array
In the downloaded files, you can find a JavaScript file generatedata.js. That is where we will create some local arrays. Please find the file and include the reference to your page. So that we can directly use that function.
Data Processing
I hope we have created the local array, now what else do we need? Yeah, you are right, we need to build the JQX Grid. To formulate it we need to start with the jQuery document ready function.
[js]
<script type=“text/javascript”>
$(document).ready(function () {
var totRecord = 30000; // If you create data from the DB, you need to get the total count from there.
var dataEntire = [];
var remainingData = [];
var pagenumber;
loadGridWithInitialData();
});
</script>
[/js]
Now the function loadGridWithInitialData() will load the grid initial data. So let us move on and check how the function works.
[js]
function loadGridWithInitialData() {
//This is where we are creating the data, as of now I am creating 1000 records initially.
dataEntire = generatedata(1000);
var source = {
localdata: dataEntire,
contentType: ‘application/json; charset=utf-8′,
datatype: “array”,
datafields: [
{ name: ‘firstname’, type: ‘string’ },
{ name: ‘lastname’, type: ‘string’ },
{ name: ‘productname’, type: ‘string’ },
{ name: ‘date’, type: ‘date’ },
{ name: ‘quantity’, type: ‘number’ }
]
};
taskOverviewDataadapter = new $.jqx.dataAdapter(source);
$(“#jqxgrid”).jqxGrid(
{
selectionmode: ‘none’,
width: ‘100%’,
source: taskOverviewDataadapter,
autoheight: true,
pageable: true,
altrows: true,
columns: [
{ text: ‘First Name’, datafield: ‘firstname’, width: 200, ‘pinned’: true },
{ text: ‘Last Name’, datafield: ‘lastname’, width: 200 },
{ text: ‘Product’, datafield: ‘productname’, width: 180 },
{ text: ‘Order Date’, datafield: ‘date’, width: 160, cellsformat: ‘dd-MMMM-yyyy’ },
{ text: ‘Quantity’, datafield: ‘quantity’, cellsalign: ‘right’, cellsformat: ‘c’ }
]
});
}
[/js]
Wow! We have created a JQX Grid successfully 🙂 Cheers!!!.
Adding data to the local array in background
As the header implies, we will create the local array in which we are appending the data in the background. The reason I say background is that we won’t let the user know we are creating an array, even after the grid loads. Shoooooo! Don’t tell anyone. 🙂 So once the grid loads, after four seconds we can start our work. Here I am using the setTimeout function.
[js]
setTimeout(function () {
remainingData = loadRemainingDataToLocalArray(dataEntire);
}, 10000);
[/js]
So here we calling a function loadRemainingDataToLocalArray and assigning the returned array in to the local array.
[js]
function loadRemainingDataToLocalArray(dataEntire) {
for (var i = 0; i < totRecord;) {
var dataCurrent = generatedata(1000);//exicute till the array count reaches our total record count which we declared at the top.
$.merge(dataEntire, dataCurrent);
i = dataEntire.length;//Setting the index here
}
return dataEntire;
}
[/js]
In this function we are using a for loop function, that will execute until the array count reaches our total record count that we have declared at the top. Cool. We created the array. 🙂
Assigning the newly created array to the JQX data source
There is no need to assign the formulated array to the grid. For that we can alter our setTimeoutfunction as follows:
[js]
setTimeout(function () {
remainingData = loadRemainingDataToLocalArray(dataEntire);
if (remainingData.length == totRecord)
loadRemainingDataToSource(remainingData);
}, 10000);
[/js]
Please note that we have called a new function loadRemainingDataToSource. In that function we are passing our new array and let us see that function now.
[js]
function loadRemainingDataToSource(remainingData) {
var source = {
localdata: remainingData,
contentType: ‘application/json; charset=utf-8′,
datatype: “array”,
datafields: [
{ name: ‘firstname’, type: ‘string’ },
{ name: ‘lastname’, type: ‘string’ },
{ name: ‘productname’, type: ‘string’ },
{ name: ‘date’, type: ‘date’ },
{ name: ‘quantity’, type: ‘number’ }
]
};
taskOverviewDataadapter = new $.jqx.dataAdapter(source);
$(“#jqxgrid”).jqxGrid(
{
selectionmode: ‘none’,
width: ‘100%’,
source: taskOverviewDataadapter,
autoheight: true,
filterable: true,
sortable: true,
pageable: true,
autorowheight: true,
altrows: true,
columnsresize: true,
columnsreorder: true,
columns: [
{ text: ‘First Name’, datafield: ‘firstname’, width: 200, ‘pinned’: true },
{ text: ‘Last Name’, datafield: ‘lastname’, width: 200 },
{ text: ‘Product’, datafield: ‘productname’, width: 180 },
{ text: ‘Order Date’, datafield: ‘date’, width: 160, cellsformat: ‘dd-MMMM-yyyy’ },
{ text: ‘Quantity’, datafield: ‘quantity’, cellsalign: ‘right’, cellsformat: ‘c’ }
]
});
$(“#jqxgrid”).jqxGrid(‘updatebounddata’);
}
[/js]
Ha ha! We have bound the new array to the grid. But are you sure the user didn’t notice we bound the grid again? What if the user was in page 2? What will happen when we re assign the grid? Yeah it will again go to page 1. If it is that way, our user will know something happened. And of course if you did this for any client, he/she will create a bug and assign it to you. 🙁 No need to be sad. I have an idea for that. Cheers.
Tracking the paging information
When we call the function loadRemainingDataToLocalArray we can get the page information as follows.
[js]
var paginginformation = $(“#jqxgrid”).jqxGrid(“getpaginginformation”);
pagenumber = paginginformation.pagenum;
[/js]
and after we re asiign the source , we need to set the page number as follows.
[js]
$(“#jqxgrid”).jqxGrid(‘gotopage’, pagenumber);
[/js]
That’s all 🙂
Complete Code
Now this is how our complete code looks like.
[html]
<!DOCTYPE html>
<html lang=“en”>
<head>
<title id=‘Description’>Load JQX Data batch by Batch – Sibeesh|Passion
</title>
<script src=“jquery-1.9.1.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxcore.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdata.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxbuttons.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxscrollbar.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxlistbox.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdropdownlist.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.filter.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.sort.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.selection.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.pager.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.columnsresize.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.columnsreorder.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxgrid.export.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdata.export.js”></script>
<script type=“text/javascript” src=“JQXItems/jqwidgets/jqxdatatable.js”></script>
<script src=“JQXItems/jqwidgets/jqxmenu.js”></script>
<link href=“JQXItems/jqwidgets/styles/jqx.base.css” rel=“stylesheet” />
<script src=“generatedata.js”></script>
<script type=“text/javascript”>
$(document).ready(function () {
var totRecord = 30000;
var dataEntire = [];
var remainingData = [];
var pagenumber;
loadGridWithInitialData();
setTimeout(function () {
remainingData = loadRemainingDataToLocalArray(dataEntire);
if (remainingData.length == totRecord)
loadRemainingDataToSource(remainingData);
$(“#jqxgrid”).jqxGrid(‘gotopage’, pagenumber);
}, 10000);
function loadGridWithInitialData() {
dataEntire = generatedata(1000);
var source = {
localdata: dataEntire,
contentType: ‘application/json; charset=utf-8′,
datatype: “array”,
datafields: [
{ name: ‘firstname’, type: ‘string’ },
{ name: ‘lastname’, type: ‘string’ },
{ name: ‘productname’, type: ‘string’ },
{ name: ‘date’, type: ‘date’ },
{ name: ‘quantity’, type: ‘number’ }
]
};
taskOverviewDataadapter = new $.jqx.dataAdapter(source);
$(“#jqxgrid”).jqxGrid(
{
selectionmode: ‘none’,
width: ‘100%’,
source: taskOverviewDataadapter,
autoheight: true,
pageable: true,
altrows: true,
columns: [
{ text: ‘First Name’, datafield: ‘firstname’, width: 200, ‘pinned’: true },
{ text: ‘Last Name’, datafield: ‘lastname’, width: 200 },
{ text: ‘Product’, datafield: ‘productname’, width: 180 },
{ text: ‘Order Date’, datafield: ‘date’, width: 160, cellsformat: ‘dd-MMMM-yyyy’ },
{ text: ‘Quantity’, datafield: ‘quantity’, cellsalign: ‘right’, cellsformat: ‘c’ }
]
});
}
function loadRemainingDataToLocalArray(dataEntire) {
var paginginformation = $(“#jqxgrid”).jqxGrid(“getpaginginformation”);
pagenumber = paginginformation.pagenum;
for (var i = 0; i < totRecord;) {
var dataCurrent = generatedata(1000);
$.merge(dataEntire, dataCurrent);
i = dataEntire.length;
}
return dataEntire;
}
function loadRemainingDataToSource(remainingData) {
var source = {
localdata: remainingData,
contentType: ‘application/json; charset=utf-8′,
datatype: “array”,
datafields: [
{ name: ‘firstname’, type: ‘string’ },
{ name: ‘lastname’, type: ‘string’ },
{ name: ‘productname’, type: ‘string’ },
{ name: ‘date’, type: ‘date’ },
{ name: ‘quantity’, type: ‘number’ }
]
};
taskOverviewDataadapter = new $.jqx.dataAdapter(source);
$(“#jqxgrid”).jqxGrid(
{
selectionmode: ‘none’,
width: ‘100%’,
source: taskOverviewDataadapter,
autoheight: true,
filterable: true,
sortable: true,
pageable: true,
autorowheight: true,
altrows: true,
columnsresize: true,
columnsreorder: true,
columns: [
{ text: ‘First Name’, datafield: ‘firstname’, width: 200, ‘pinned’: true },
{ text: ‘Last Name’, datafield: ‘lastname’, width: 200 },
{ text: ‘Product’, datafield: ‘productname’, width: 180 },
{ text: ‘Order Date’, datafield: ‘date’, width: 160, cellsformat: ‘dd-MMMM-yyyy’ },
{ text: ‘Quantity’, datafield: ‘quantity’, cellsalign: ‘right’, cellsformat: ‘c’ }
]
});
$(“#jqxgrid”).jqxGrid(‘updatebounddata’);
}
});
</script>
</head>
<body class=‘default’>
<div id=“jqxgrid”></div>
</body>
</html>
[/html]
Output
Happy Coding 🙂 Please like and share, if you liked this article. Please provide your valuable comments.
Kindest Regards
Sibeesh Venu
