In my JSF's datatable I have implemented lazy loading and when I paginate through records it is taking time about 4 or 5 seconds to execute next set of records, actually it should be take less than a second to execute the results.
This has happened to the way I have implemented it, not sure how could I resolve this.
DataModel class which extends LazyDataModel
@Override
public List<Request> load(int startingAt, int maxPerPage, String sortField,
SortOrder sortOrder, Map<String, String> filters)
{
requestList = requestService.getRequest(startingAt, maxPerPage,
sortField, sortOrder, filters);
this.setRowCount(requestList.size());
if (requestList.size() > maxPerPage)
{
System.out.println("executing");
return requestList.subList(startingAt, startingAt + maxPerPage);
}
else
{
System.out.println("executing else ");
return requestList;
}
return requestList;
}
and in dao class
@Override
public List<Request> getRequest(int startingAt, int maxPerPage,
String sortField, SortOrder sortOrder, Map<String, String> filters)
{
Criteria criteria = sessionFactory.getCurrentSession().createCriteria(
Request.class);
criteria.addOrder(Order.desc("requestNo"));
for (Map.Entry<String, String> entry : filters.entrySet())
{
if (entry.getValue() != null)
{
criteria.add(Restrictions.ilike("requestNo",
"%" + entry.getValue() + "%"));
}
}
//criteria.setMaxResults(maxPerPage);
//criteria.setFirstResult(startingAt);
return criteria.list();
}
Could someone explain what caused this delay in paginating through the records?
If I remove the following
if (requestList.size() > maxPerPage)
{
System.out.println("executing");
return requestList.subList(startingAt, startingAt + maxPerPage);
}
else
{
System.out.println("executing else ");
return requestList;
}
and execute, then it is executes perfectly without delay, however the problem is this.setRowCount(requestList.size());
always 5 which is my default number of records per page.
Update 2
@Override
public List<Request> load(int startingAt, int maxPerPage, String sortField,
SortOrder sortOrder, Map<String, String> filters) {
requestList = requestService.getRequest(startingAt, maxPerPage,
sortField, sortOrder, filters);
this.setRowCount(requestService.getRequestCount());
if (requestService.getRequestCount() > maxPerPage) {
try {
return requestList.subList(startingAt, startingAt + maxPerPage);
} catch (IndexOutOfBoundsException e) {
//e.printStackTrace();
return requestList.subList(startingAt, startingAt
+ (requestService.getRequestCount() % maxPerPage));
}
} else {
return requestList;
}
}
Used a different query for getting count of resultset using the following
@Override
public int count() {
int count = ((Long) sessionFactory.getCurrentSession()
.createQuery("select count(*) from Request").uniqueResult())
.intValue();
System.out.println(" count size " + count);
return count;
}
and my dao
@Override
public List<Request> getRequest(int startingAt, int maxPerPage,
String sortField, SortOrder sortOrder, Map<String, String> filters) {
Criteria criteria = sessionFactory.getCurrentSession().createCriteria(
Request.class);
criteria.addOrder(Order.desc("requestNo"));
for (Map.Entry<String, String> entry : filters.entrySet()) {
if (entry.getValue() != null) {
criteria.add(Restrictions.ilike("requestNo",
"%" + entry.getValue() + "%")); }
}
criteria.setMaxResults(maxPerPage);
criteria.setFirstResult(startingAt);
return criteria.list();
}
In case of very large resulting lists, the Java-side counting and the sublisting operations can be dangerous for the memory usage and consequently also on the performance side.
Instead, I usually go with the following approach: use 2 queries, one for counting the filtered resultSet (I let the db do the count), and another one for retrieving the paginated resultSet (I let the db extract the sublist). I have never experienced significant delays, even with tables containing millions of rows.
Follows a concrete example with sorting and filtering. All the code uses JPA standard (no Hibernate or Spring custom features) The CriteriaQuery
approach is particularly indicated in such situations.
MyBean class
@ManagedBean
@ViewScoped
public class MyBean {
@EJB
private MyObjFacade myObjFacade;
private LazyDataModel<MyObjType> model; // getter and setter
@PostConstruct
public void init() {
model = new LazyDataModel<MyObjType> () {
@Override
public List<MyObjType> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, String> filters) {
model.setRowCount(myObjFacade.count(filters));
return myObjFacade.getResultList(first, pageSize, sortField, sortOrder, filters);
}
};
model.setRowCount(myObjFacade.count(new HashMap<String, String> ()));
}
}
MyObjFacade class
@Stateless
public class MyObjFacade {
@PersistenceContext
private EntityManager em;
@EJB
private MyObjFacade myObjFacade;
private Predicate getFilterCondition(CriteriaBuilder cb, Root<MyObjType> myObj, Map<String, String> filters) {
Predicate filterCondition = cb.conjunction();
String wildCard = "%";
for (Map.Entry<String, String> filter : filters.entrySet()) {
String value = wildCard + filter.getValue() + wildCard;
if (!filter.getValue().equals("")) {
javax.persistence.criteria.Path<String> path = myObj.get(filter.getKey());
filterCondition = cb.and(filterCondition, cb.like(path, value));
}
}
return filterCondition;
}
public int count(Map<String, String> filters) {
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<MyObjType> myObj = cq.from(MyObjType.class);
cq.where(myObjFacade.getFilterCondition(cb, myObj, filters));
cq.select(cb.count(myObj));
return em.createQuery(cq).getSingleResult().intValue();
}
public List<MyObjType> getResultList(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, String> filters) {
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<MyObjType> cq = cb.createQuery(MyObjType.class);
Root<MyObjType> myObj = cq.from(MyObjType.class);
cq.where(myObjFacade.getFilterCondition(cb, myObj, filters));
if (sortField != null) {
if (sortOrder == SortOrder.ASCENDING) {
cq.orderBy(cb.asc(myObj.get(sortField)));
} else if (sortOrder == SortOrder.DESCENDING) {
cq.orderBy(cb.desc(myObj.get(sortField)));
}
}
return em.createQuery(cq).setFirstResult(first).setMaxResults(pageSize).getResultList();
}
}