Hibernation problem

Question

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

First option

My hibernate pojo classes are 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 retrieve the stock, the list of characteristic records is linked and the result is received.

My stock chart contains more than 1 million records and each stock associated with 10 characteristics (so Stock_characteristics contains more than 10 million rows). When we get the whole result, the relationship between stock and performance can slow down.

The 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 } 

Specifications are the 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; } 

To get my result set, I pass only a set of characteristics. For example, if the throughput characteristic is 2, then first I find stock identifiers that have both characteristics. Then I will project stock data from the stock class. Here is my sample code for the 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 that a connection request is being made between Stock and Feature, which can slow down and

here is my sample code for the 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(); } 

What is the software point of view, which is the best choice? or which one should I use to improve performance?

Answer 1

I think you should display all relations between entities in sleep mode. This will help if you want to use hql and criteria, otherwise you will not be able to connect between objects.

For performance, mark all your mappings as LAZY and read the following:

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

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