Jqgrid huge data load problems

Question

I am doing some investigation on jqgrid, everything works fine, until I load the huge data which contains about 1M lines in database, jqgrid don't display now, when I downsize the lines of the database to 100K, the data will display, but I still needs to refresh the page several times, I downsize to 10K, it works fine, I am not sure the jqgrid has a size limited? and more, I can save the data to local file which returns by the url, and upload it to the server, and the change the url to the file and jqgrid can display it....

Can anybody help me out? Code is here:

    <script type="text/javascript"> 
            $.jgrid.no_legacy_api = true;
            $.jgrid.useJSON = true;
            $(function() {
                $("#griddisplay").jqGrid({
                    url:'<spring:url value="/charts/search/sub"/>',
                    datatype: 'json',
                    mtype:'POST',
                    colNames:['id','time_stamp', 'user_name','name','parameter'],
                    colModel:[
                        {name:'id',index:'id',width:160},
                        {name:'time_stamp',index:'time_stamp',width:160},
                        {name:'user_name',index:'user_name',width:160},
                        {name:'name',index:'name',width:160},
                        {name:'parameter',index:'parameter', width:100}
                    ],          
                    rowNum:100,
                    rowList:[100,50,25],
                    height: 500,
                    autowidth: true,
                    rownumbers: true,
                    pager: '#pager',
                    sortname: 'time_stamp',
                    gridview: true,
                    //viewrecords: true,
                    sortorder: "asc",
                    //emptyrecords: "<fmt:message key='msg.report.table.noreport' />",
                    loadonce: false,
                    //multiselect: false,
                    //loadComplete: function() {
                    //},
                    //caption: "Video Grid",
                    jsonReader: {
                        repeatitems: false,
                        id: "id",
                        root: "rows",
                        page: "page",
                        total: "total",
                        records: "records"
                    }
                });
            });
    </script>

the json data is below:

{
    "page": 1,
    "total": 2,
    "records": 2,
    "rows": [
        {
            "id": 9901,
            "time_stamp": "2011-04-12",
            "user_name": "abcd",
            "name": "somehere",
            "parameter": "harry"
        },
        {
            "id": 9902,
            "time_stamp": "2011-04-12",
            "user_name": "abcd",
            "name": "somehere",
            "parameter": "harry"
        }
    ]
}

get all the counts from oracle database

@Transactional(readOnly = true)
public long getLogbaksRecords() {
    String sql="select count(*) from test";
    long lbs=jdbcTemplate.queryForLong(sql);
    System.out.println("lbs="+lbs);
    return lbs;
}

get the data

@Transactional(readOnly = true)
public List<LogbakBean> getLogbaks(String sidx, String sord, String rows,int p) {
    String sql="SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT time_stamp,parameter,user_name,name FROM test order by "+sidx+" "+sord+") A WHERE ROWNUM <= "+p*Integer.valueOf(rows)+")WHERE RN > "+(p-1)*Integer.valueOf(rows);
    System.out.println("query start"+sql);
    List<LogbakBean> lbs=jdbcTemplate.query(sql, new RowMapper<LogbakBean>(){

        public LogbakBean mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            //LOG.info("getLogbaks===mapRow");
            LogbakBean lb=new LogbakBean();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String time=sdf.format(new Date(Long.valueOf(rs.getString("time_stamp"))));
            lb.setTime_stamp(time);
            lb.setParameter(rs.getString("parameter"));
            lb.setUser_name(rs.getString("user_name"));
            lb.setName(rs.getString("name"));
            lb.setId(Long.valueOf(rs.getString("RN")));
            //System.out.println("curt rows="+rs.getString(5));
            //LOG.info("getLogbaks===mapRow===finish");
            return lb;
        }

    });
    System.out.println("query end");
    return lbs;
}

the web controller which returns data to jqgrid

@RequestMapping(value="/charts/search/sub",method=RequestMethod.GET)
public void searchResult(
        HttpServletRequest request,
        HttpServletResponse response,
        @RequestParam("page")String page,
        @RequestParam("rows")String rows,
        @RequestParam("sidx")String sidx,
        @RequestParam("sord")String sord) throws IOException{

    int p= Integer.parseInt(page);
    int t=Integer.parseInt(rows);
    System.out.println("p="+p+"t="+t);
    long logs=services.getLogbaksRecords();
    JSONObject obj=new JSONObject();
    obj.put("page", p);
    obj.put("total",logs%t==0?logs/t:logs/t+1);
    obj.put("records",logs);
    System.out.println("total records="+logs);
    JSONArray arr=new JSONArray();
    List<LogbakBean> lgb=services.getLogbaks(sidx,sord,rows,p);
    for(int i=0;i<lgb.size();i++){
        JSONObject o=new JSONObject();
        LogbakBean l=lgb.get(i);
        o.put("id", l.getId());
        o.put("time_stamp", l.getTime_stamp());
        o.put("user_name",l.getUser_name());
        o.put("name",l.getName());
        o.put("parameter",l.getParameter());
        arr.add(o);
        LOG.info(l.getTime_stamp()+","+l.getUser_name()+","+l.getName()+","+l.getParameter());
    }
    obj.put("rows", arr);
    response.setContentType("text/html;charset=utf-8");
    response.getWriter().write(obj.toString());
    //OutputFormat format = OutputFormat.createCompactFormat();
    //JsonWriter writer = new JsonWriter();
    //writer.
    //return obj;

}

Answer 1

I think you shouldn't transfer such amount of data through the wire.
jqGrid uses a paging system which allows you to return chunks of data to the grid.

Basically what I would do is implement some sort of pagination of your data server-side.

Answer 2

Finally, the problems resolved, just because i use multiselect in the grid page, the multiselect is conflict with the jqgrid, please see the code i update above. tanks Oleg and leftyX

the conflict code is below

$(function(){
    $.localise('ui-multiselect', {/*language: 'zh',*/ path: '<spring:url value="/resources/scripts/multiselect/" />'});
    $(".multiselect").multiselect();
});

when i removed the code, the problems resolved. thanks everyone.