Spring Data Jpa query resultset to object creation is very slow

Question

I have a domain object,defined as :

@AllArgsConstructor
@NoArgsConstructor
@ToString
@Getter
@Setter
@Entity
@Table(name = "t_domin")
@Builder
public class Domain {

  @Column(name = "BUSINESS_DATE")
  private LocalDateTime businessDate;

  @Column(name = "DATA_PROVIDER")
  @Enumerated(EnumType.STRING)
  private DataProvider dataProvider;

}

For the above domain Object I've Spring Repository:

@Repository
public interface DomainRepository extends JpaRepository<Domain, UUID> {

  List<Domain> findByBusinessDate(LocalDateTime businessDate);
}

when I'm querying : repository.findByBusinessDate(someDate) --> I get 1000 records in more than a minute,where as same query if I run on db(oracle) I get the result-set within a sec.

I turned on the log (TRACE) ,I see that its taking more time in extracting the result set into list of java object:

TRACE o.h.type.descriptor.sql.BasicExtractor

How can i tune the performance ?

Answer 1

Hibernate default fetch size is 10 ,which was causing the issue . For large result sets, it’s important to increase the fetch size,so by changing the fetch size at query level its become really faster.

@QueryHints(@javax.persistence.QueryHint(name = "org.hibernate.fetchSize", value = "1000"))
List<Domain> findByBusinessDate(LocalDateTime businessDate);

If you want it globally add this in your property file:

hibernate.jdbc.fetch_size:1000