Can I implement lazy loading with jqGrid?

问题:

I have a grid with over 5000 data records. This data keeps growing on a daily basis. When I load the page with the grid, it takes almost a minute before the grid shows the data which I have to display 10 rows at a time.

Is it possible then to implement lazy loading with this jqGrid?

This is my action to generate the JSon String:

@RequestMapping(value = "studentjsondata", method = RequestMethod.GET)
public @ResponseBody String studentjsondata(HttpServletRequest httpServletRequest) {
    Format formatter = new SimpleDateFormat("MMMM dd, yyyy");
    String column = "id";
    if(httpServletRequest.getParameter("sidx") != null){
        column = httpServletRequest.getParameter("sidx");
    }
    String orderType = "DESC";
    if(httpServletRequest.getParameter("sord") != null){
        orderType = httpServletRequest.getParameter("sord").toUpperCase();
    }
    int page = 1;
    if(Integer.parseInt(httpServletRequest.getParameter("page")) >= 1){
        page = Integer.parseInt(httpServletRequest.getParameter("page"));
    }
    int limitAmount = 10;
    int limitStart = limitAmount*page - limitAmount;
    List<Student> students = Student.findAllStudentsOrderByColumn(column,orderType,limitStart,limitAmount).getResultList();  
    List<Student> countStudents = Student.findAllStudents();
    double tally = Math.ceil(countStudents.size()/10.0d);
    int totalPages = (int)tally;
    int records = countStudents.size();


    StringBuilder sb = new StringBuilder();
    sb.append("{\"page\":\"").append(page).append("\", \"records\":\"").append(records).append("\", \"total\":\"").append(totalPages).append("\", \"rows\":[");
    boolean first = true;
    for (Student s: students) {
        sb.append(first ? "" : ",");
        if (first) {
            first = false;
        }
        sb.append(String.format("{\"id\":\"%s\", \"cell\":[\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\"]}",s.getId(), s.getId(), s.getFirstName(), s.getLastName(),  formatter.format(s.getDateOfBirth().getTime()), s.getGender(), s.getMaritalStatus()));
    }
    sb.append("]}");
    return sb.toString();
}

And this is page with the jqGrid:

$("#studentGrid").jqGrid({
            url: '/starburst/programmes/studentjsondata',
            datatype: 'json',
            height: 'auto',
            colNames:['id','First Name', 'Last Name', 'Date Of Birth', 'Gender', 'Marital Status'], 
            colModel:[ 
                {name:'id',index:'id', width:15}, 
                {name:'firstName',index:'firstName', width:30, formoptions:{elmprefix:'(*) '}, editable:true, edittype: 'text', editrules:{required:true}},
                {name:'lastName',index:'lastName', width:30, formoptions:{elmprefix:'(*) '}, editable:true, edittype: 'text',editrules:{required:true}},
                {name:'dateOfBirth',index:'dateOfBirth', width:30, formoptions:{elmprefix:'(*) '},editrules:{required:true}, editable:true, edittype: 'text',               
                    editoptions: {
                        dataInit: function(element) {
                            $(element).datepicker({dateFormat: 'MM dd, yy'})
                        }
                    } 
                },                    
                {name:'gender',index:'gender', width:30, formoptions:{elmprefix:'(*) '}, editable:true, editrules:{required:true}, edittype: 'select',
                    editoptions:{value:{}}
                },
                {name:'maritalStatus',index:'maritalStatus', width:30, formoptions:{elmprefix:'(*) '}, editable:true, editrules:{required:true}, edittype: 'select',
                    editoptions:{value:{}}
                }                    
            ],
            rowNum:10,
            autowidth: true,
            pager: '#pager', 
            sortname: 'id', 
            viewrecords: true, 
            sortorder: "desc",
            caption:"Students",
            emptyrecords: "Empty Records",
            subGrid: true,
            /* <![CDATA[ */ 
            onSelectRow: function(id){ 
                if((lastsel != 0)&&(id!==lastsel)){ 
                    $("#studentGrid").jqGrid('collapseSubGridRow', lastsel);                
                } 
                lastsel=id; 
            }/* ]]> */ 
        });
        $("#studentGrid").jqGrid('navGrid', "#pager", {edit:false,add:false,del:false,search:true},{ },{ },{ },
        { 
            sopt:['eq', 'ne', 'lt', 'gt', 'cn', 'bw', 'ew'],
            closeOnEscape: true, 
            multipleSearch: true, 

See Query Below:

public static TypedQuery<tt.edu.sbcs.model.Student> findAllStudentsOrderByColumn(String column, String orderType, int limitStart, int limitAmount) {
    EntityManager em = Programme.entityManager();
    TypedQuery<Student> q = em.createQuery("SELECT o FROM Student AS o ORDER BY"+" "+column+" "+orderType, Student.class);
    q.setFirstResult(limitStart);//used to skip the first "N"  elements form the result set, it indexes results form zero 
    q.setMaxResults(limitAmount);
    return q;
}

The limit amount i set in the action above. this value is 10.

回答1:

OK, so here is a partial answer based on JPA (but I guess that it should be almost trivial to adapt it to Hibernate). You should be able to do something like this to fetch only the necessary objects :

Query query = em.createQuery("select o from " + "Student"+ " as o order by o.id");
query.setFirstResult(start);
query.setMaxResults(end - start);
return query.getResultList();

For a count, something like this should do it:

Number count = (Number) em.createQuery("select count(id) from " + "Student").getSingleResult();
if (count == null) {
    count = Integer.valueOf(0);
}
return count.intValue();

Will edit when I have more info.

回答2:

First of all I find loading of 10 rows from 5000 total in 1 minute is EXTREMELY slow. I think that the server code and not jqGrid in the bottleneck in your case.

The first very suspected line of your code is

List<Student> countStudents = Student.findAllStudents();

You need to get only the number of Students, but it seems that you get all properties of all students and then use countStudents.size() in two next lines. Maximum what should be done is something like

SELECT COUNT(*) FROM dbo.Students

Instead of this it meens you do SELECT * FROM dbo.Students.

It can be that you have serious problems in the database or in the implementation of the function findAllStudentsOrderByColumn if your code take 1 minute. Probably you have some classes which represent entity model or database model. If you get so bad performance you have to examine the code which do the database access very carefully or to consider to use some more direct access to the database where you can specify the database queries directly. I'm not Java or Spring developer, but I can definitively say, that if the request of 10 rows from 5000 get more as 1 second than it's already too slow.

It looks like you need to return some columns from one table inclusive the id. So you can get the data with SELECT like

SELECT TOP(10) id, firstName, lastName, dateOfBirth, gender, maritalStatus
FROM dbo.Students
ORDER BY id

to get the first page of data and something like the following

WITH GetAll (id, firstName, lastName, dateOfBirth, gender, maritalStatus) AS (
    SELECT id, firstName, lastName, dateOfBirth, gender, maritalStatus
    FROM dbo.Students
    ORDER BY id
), GetTop (id, firstName, lastName, dateOfBirth, gender, maritalStatus) AS (
    SELECT TOP(20) * FROM GetAll -- skip 2 pages per 10 rows
), GetNext (id, firstName, lastName, dateOfBirth, gender, maritalStatus) AS (
    SELECT TOP(10) a.* FROM GetAll AS a
        LEFT OUTER JOIN GetTop AS t ON t.id = a.id
    WHERE t.id IS NULL
)
SELECT * FROM GetNext

for all next pages. I used common table expression (CTE) syntax, but you can use sub-queries if your database don't support it.

Because you allow to sort by every column you should create INDEXes on every column in the table to improve sorting performance. (I think that the Students table will be not modified with a lot of changes per second, so the indexes will not reduce the performance of the table).

Another thing what you should to consider is to change the serialization to JSON. The usage of String.format("\"%s\", someString) is dangerous. There are some characters which must be escaped with \ character. I mean " and \. You should do this to make the code safe. Common practice is to use some standard classes existing in your language for the serialization (see here or here for example).

The next tip is to use jsonReader: {cell: ""} and return the data for the row in the form

["%s", "%s", "%s", "%s", "%s", "%s"]

instead of

{"id":"%s", "cell":["%s", "%s", "%s", "%s", "%s", "%s"]}

You will don't send id value twice and will don't send strings "id" "cell" and some other unneeded characters ('{', ':', ...).

On the client side you should always use gridview: true jqGrid option. With 10 rows of data you will don't see serious differences, because jqGrid will be very quickly, but with more rows the difference will be very clear.

The last suggestion: you should use formatter: 'date' and send the date in the ISO 8601 format: like 2012-03-20.