I am using Spring Boot for creating Microservice. I have a basic entity object like below and its corresponding repository class and it fetches close to 400 records. It takes more time than expected. There is no joins involved here.
Entity.java
@Entity
@Table(name="VW_EMPLOYEE", schema="emp")
public class Employee {
@EmbeddedId
private EmployeeId id;
@column("amount1")
private BigDecimal amt1;
@column("amount2")
private BigDecimal amt2;
@column("amount3")
private BigDecimal amt3;
@column("amount4")
private BigDecimal amt4;
...
}
EmployeeId.java
@Embeddable
public class EmployeeId {
@column(name="EmpName")
private String empName;
@column(name="DOB")
private LocalDate dob;
}
EmployeeRepository.java
@Repository
public interface EmployeeRepository extends PagingAndSortingRepository<Employee, EmployeeId> {
@Query("select a from Employee a where a.id.empName= :empName and a.id.dob between :startDate and :endDate")
public Optional<List<Employee>> findEmployees(String empId, LocalDate startDate, LocalDate endDate);
}
When I call the repository method, employeeRepository.findEmployees(...)
, it fetches close to 400 records. I see only single query getting generated in log file. But it takes more time than expected. In logs I see below lines,
[org.hibernate.engine.internal.TwoPhaseLoad][initializeEntityEntryLoadedState][Resolving Attributes for com.Employee#component [employeeName, dob]{employeeName="...", dob="..."}]
[Processing Attribute `amt1`: value='133432233']
[Attribute (`column1`) - enhanced for Lazy Loading? - false]
Attributes for com.Employee#component [employeeName, dob]{employeeName="...", dob="..."}]
[Processing Attribute `amt2`: value='48899877']
[Attribute (`column1`) - enhanced for Lazy Loading? - false]
Attributes for com.Employee#component [employeeName, dob]{employeeName="...", dob="..."}]
[Processing Attribute `amt3`: value='334422123']
[Attribute (`column1`) - enhanced for Lazy Loading? - false]
Attributes for com.Employee#component [employeeName, dob]{employeeName="...", dob="..."}]
[Processing Attribute `amt4`: value='44322112']
[Attribute (`column1`) - enhanced for Lazy Loading? - false]
I see above 4 statements for 400 records. Is it taking more time for mapping the DB data with Entity collections I think. Time it takes for 400 records is close to 2-3 seconds. I want to bring it down to less than a second.
>` doesn't really make sense, the list is either empty or filled (so please remove the optional). You want to increase the JDBC fetch size, the default is 10, which means it does around 40 roundtrips to your database server, as this takes time it will slow down retrieval. You can use `@QueryHints({@QueryHint(name="org.hibernate.fetchSize", value="100"))}` on your query method. If that doesn't help, check your query execution plan, and introduce some indexes to speed up the query.
– M. Deinum Jan 31 '22 at 07:01