Hibernate Performance issue

Question

I am very new to Hibernate. Here I would like to compare two option.

First option

My hibernate pojo classes as follows.

Stock {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "stock_id")
  private Long stockId;

  @Column(name = "stock_name")
  private String stockName;

  @ManyToMany(fetch = FetchType.EAGER)
  @JoinTable(name = "stock_characteristics", joinColumns =  {@JoinColumn(name = "stock_id")}, inverseJoinColumns = {@JoinColumn(name = "ct_id")})
  private List<Characteristic> characteristics = new ArrayList<>();

  //constructor, getters and setters

}

Characteristics {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ct_id", nullable = false)
  private Long id;

  @Column(name = "name", nullable = false, length = 32)
  private String name;

  //constructor, getters and setters
}

Each stock contains a list of characteristics. Whenever I fetch the stock, list of characteristic entries will associate and result is getting.

My stock table contains over 1 million records and each stock associated with 10 characteristic (so stock_characteristics contain more than 10 million rows). When we fetch the entire result, the association between stock and Characteristic may get slow.

Second option.

I rewrite my pojo classes as follows.

Stock {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "stock_id")
  private Long stockId;

  @Column(name = "stock_name")
  private String stockName;

  //constructor, getters and setters

}

Characteristics - same as above and

StockCharacteristics {
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      @Column(name = "id")
      private Long id;
      @Column(name = "stock_id", nullable = false)
      private Long stockId;
      @Column(name = "ct_id", nullable = false)
      private Long ctId;
    }

For getting my result set, I am passing only the set of characteristics. For example if pass characteristic as 2, then first I find the stock ids which having both characteristics. Then I will project the stock details from Stock class. Here is my sample code for first option.

    criteria.createAlias("stock.characteristics", "stockCharacteristics",       CriteriaSpecification.INNER_JOIN).add(Restrictions.in("stockCharacteristics.id", listOfSelectedCharacteristics));
    List<Object[]> projectedList = criteria.setProjection(Projections.projectionList().add(Projections.count("id")).add(Projections.groupProperty("id"))).list();
    List<Long> stockIdList = new ArrayList<>();
    for(Object[] entry: projectedList){
      if(((Long) entry[0]).intValue() == listOfSelectedCharacteristics.size()){
           stockIdList.add((Long)entry[1]);
      }
    }

    if(!stockIdList.isEmpty()){
       Criteria criteriaWithCharacteristics =   getDb(true).createCriteria(Stock.class, "stock").add(Restrictions.in("id", stockIdList));
       selectedStocks = criteriaWithCharacteristics.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
    }

Here you can see a join query is performed between Stock and Characteristic which may slow the down and

here is my sample code for second option

List<Object[]> stockIdList = //gets the stock id list from StockCharacteristics
    if(!stockIdList.isEmpty()){ 
       Criteria criteriaWithCharacteristics =   getDb(true).createCriteria(Stock.class, "stock").add(Restrictions.in("id", stockIdList));
       selectedStocks = criteriaWithCharacteristics.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
    }

As a program point of view which is the best option to select? or which one I should use for better performance?

score 0 · Answer 1 · answered Aug 02 '16 at 08:04

I think that you should map in hibernate all the relation between entities. It will help if you want to use hql and criteria otherwise you won't be able to do join between entities.

For the performance, mark all your mapping as LAZY and read this :

https://zeroturnaround.com/rebellabs/how-to-use-jpa-correctly-to-avoid-complaints-of-a-slow-application/

I often use MS SQL server and check the execution plan of my slow query to ensure that my query is well indexed. With mysql you can use "show index"

Hibernate Performance issue

1 Answers1