Hibernate query.list() taking long time to respond

Question

I am working on a REST server which accepts and returns JSON data. For a particular input values(from input JSON) Hibernate (integrated with Spring) query the database and send JSON back to Rest Controller. Client application is designed to have timeout of 5 sec, for reading response, which cannot be altered.

Now the problem is sometimes (not always but mostly) the Hibernate is unable to process data within given time limit thus time out error is received on client side.

I checked with other post on net to use full constructor in bean classes and to have lazy loading. Both are true in my case. Below is one of the DAOImpl method causing this problem. Here i have to query 2 tables (which doesn't have much data, approx 20 entries in each table), add all data to a json array and send back.

@Override
   public String getOfferOnRuleNameBalance(String inputJsonString) {
    JSONObject saveJsonObject = new JSONObject(inputJsonString);
    String ruleName    =   saveJsonObject.getString("rulename");
    int currentAccountBalance    =   saveJsonObject.getInt("currentAccountBalance");
    Session session1 = getSession();
    Criteria criteria =session1.createCriteria(PaymentPlanOffers.class);
    criteria.add(Restrictions.eq("rulename", ruleName));
    @SuppressWarnings("unchecked")
    List<PaymentPlanOffers> offerList=criteria.list();
    JSONArray jsonArray = new JSONArray();
    for(PaymentPlanOffers object:offerList)
    {
        JSONObject jsonObject1 =new JSONObject();
        jsonObject1.put("instAmount",object.getAmountPercent());
        jsonObject1.put("instNumber", object.getNumInstallment());
        jsonObject1.put("frequency", object.getFrequency());
        jsonObject1.put("offerId", object.getId());
        jsonObject1.put("offerName", object.getOfferName());
        jsonObject1.put("active", object.isActive());
        jsonObject1.put("accepted", object.isAccepted());
        jsonObject1.put("totalAmount", currentAccountBalance);
        jsonObject1.put("startDate", object.getStartDate());
        jsonObject1.put("endDate", object.getEndDate());
        jsonArray.put(jsonObject1);
    }

    Criteria criteria2 =session1.createCriteria(CustomPlanOffer.class);
    criteria2.add(Restrictions.eq("rulename", ruleName));
    @SuppressWarnings("unchecked")
    List<CustomPlanOffer> customOfferList=criteria2.list();
    for(CustomPlanOffer object:customOfferList)
    {
        JSONObject jsonObject1 =new JSONObject();
        jsonObject1.put("instAmount", object.getAvgInstallment());
        jsonObject1.put("instNumber", object.getNumOfInstallment());
        jsonObject1.put("frequency", object.getFrequency());
        jsonObject1.put("offerId", object.getId());
        jsonObject1.put("offerName", object.getName());
        jsonObject1.put("active", object.isActive());
        jsonObject1.put("accepted", object.isAccepted());
        jsonObject1.put("totalAmount", object.getTotalPaymentAmount());
        jsonObject1.put("startDate", object.getStartDate());
        jsonObject1.put("endDate", object.getEndDate());
        jsonArray.put(jsonObject1);
    }

    JSONObject mainObj = new JSONObject();
    mainObj.put("allOffers", jsonArray);
    session1.close();
    return mainObj.toString();
}

Please let me know if i have implemented it in correct way.

EDIT : Posting another method causing similar issue with all modifications done

@Override
public String getAllOffers(String inputJsonString) {
    Session session = getSession();
    Transaction t = session.beginTransaction();
    String hql = "FROM PaymentPlanOffers";
    Query query = session.createQuery(hql);
    @SuppressWarnings("unchecked")

    List<PaymentPlanOffers> results = query.list(); //this is where it goes on hang


    JSONArray jsonArray = new JSONArray();
    for(PaymentPlanOffers object:results)
    {
        JSONObject jsonObject1 =new JSONObject();
        jsonObject1.put("offername", object.getOfferName());
        jsonObject1.put("rulename", object.getRulename());
        jsonObject1.put("id", object.getId());
        jsonObject1.put("offerMessage", object.getOfferMessage());
        jsonArray.put(jsonObject1);
    }
    JSONObject mainObj = new JSONObject();
    mainObj.put("allOffers", jsonArray);
    t.commit();
    session.close();
    return mainObj.toString();
}

Answer 1

The first thing I would do is to log the SQL queries being generated (see here). Once you have them you can do some more digging:

• Are you sure the database itself isn't the problem? Bad table and/or join indexes are a common cause.
  • Log the start and end time of getOfferOnRuleNameBalance
  • Log the start and end time of when you do each query
  • Check to see if generated SQL queries perform poorly when manually run against the database
• Are your hibernate mappings and code causing an N+1 select? You will know if there are multiple SELECT statements generated for each item in a lazy loaded collection. This could also be caused by a loop somewhere, or caused by an equals(), toString(), or hashCode() method accessing the lazy loaded collection and checking each element in it.

Logging each query will also tell you if it's only one that's causing the problem or if it's both queries.

For more troubleshooting I would have to see the mappings for the hibernate objects themselves but this may at least give you a starting point.