CODEWITHSUNDEEP
javahibernatejpaspring-data-jpahibernate-mapping
Uri Loya
Uri Loya

Reputation: 1391

Using lists as columns results in bad performance - JPA

I am using hibernate and I have noticed that when using lists as columns, fetching from the database (findById) with a JpaRepository takes a lot of time. In the logs I have found that the query is correct:

select
    measuremen0_.data_id as dat1_3_0_,
    measuremen0_.measurements as measurem2_3_0_,
    measuremen0_.measurements_order as measurem3_0_ 
from
    data_measurements measuremen0_ 
where
    measuremen0_.data_id=?

but after the query there is an initialization which takes a lot of time, even move than the query itself: 

Preparing collection initializer:
Found row of collection:
Found row of collection:
Found row of collection:
... (times number of measurements)

What is the purpose of the collection preparing? can it be skipped? Here is my entity class:

@Entity
public class Data {

@Id
private long id;

@ElementCollection(fetch = FetchType.EAGER)
@OrderColumn
@Column(columnDefinition = "TEXT", updatable = false)
private List<String> measurements;

Upvotes: 0

Views: 187

Answers (1)

Paulo Ara&#250;jo
Paulo Ara&#250;jo

Reputation: 569

Eagerly load any collection may lead to bad performance. Note that most persistence providers will need at least N + 1 SELECTs to the database to fill your data - the first one to retrieve all owner entities plus one SELECT for each list that needs to be filled into each entity. If possible in your requirements, remove the eager fetch and fetch the lists after selecting a given Data.

The usage of TEXT as columnDefinition may have a big impact also because some databases, instead of keeping the data in normal columns, moves each register to own registers or files. If you can, exchange your column definition for a length definition, which will make database able to better persist phisically your data and prune the queries afterward.

Upvotes: 1

Related Questions