Optimization relationships beetween entityes

user_6817506 0
Viewed 58

I have entity like:

@Entity
@Table(name = "test")
public class TestEntity {

@ElementCollection
@Column(name = "record_type_id", nullable = false)
@CollectionTable(name = "test_entity_record_type", joinColumns = @JoinColumn(name = "test_entity_id"))
private Set<RecordType> recordTypes = new HashSet<>();

@ElementCollection
@Column(name = "record_type_id", nullable = false)
@CollectionTable(name = "test_entity_record_type1", joinColumns = @JoinColumn(name = "test_entity_id"))
private Set<RecordType> recordTypes1 = new HashSet<>();

@ElementCollection
@Column(name = "record_type_id", nullable = false)
@CollectionTable(name = "test_entity_record_type2", joinColumns = @JoinColumn(name = "test_entity_id"))
private Set<RecordType> recordTypes2 = new HashSet<>();
}

I have 1000 records of entity in DB. When i try to get 1000 entityes i see in logs:

  • 1000 select to entity table
  • 1000 select to test_entity_record_type table by test_entity_id
  • 1000 select to test_entity_record_type1 table by test_entity_id
  • 1000 select to test_entity_record_type2 table by test_entity_id

As result time of answer is very long.

How can i reduce the response time?

spring-data jpa spring java
2 Answers

You can use @BatchSize annotation:

@BatchSize(size = 1000)
private Set<RecordType> recordTypes = new HashSet<>();
user_6330120

I redid the same test case, is the following configuration giving me a shorter time than the first one (properties file):

spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.implicit-strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true
spring.jpa.properties.hibernate.cache.use_second_level_cache=false
spring.jpa.properties.hibernate.cache.use_query_cache=false
spring.jpa.properties.hibernate.generate_statistics=false
spring.jpa.properties.hibernate.jdbc.batch_size=1000
spring.jpa.properties.hibernate.jdbc.fetch_size=1000
spring.jpa.properties.hibernate.order_inserts=true
spring.datasource.maxActive=5
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true
spring.jpa.properties.hibernate.query.in_clause_parameter_padding=true

spring.datasource.hikari.poolName=Hikari
spring.datasource.hikari.auto-commit=false
spring.datasource.hikari.data-source-properties.cachePrepStmts=true
spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=2048
spring.datasource.hikari.data-source-properties.useServerPrepStmts=true

hope this would be helpful.

user_84004930
Related