1

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Lolly
  • 34,250
  • 42
  • 115
  • 150
  • https://www.baeldung.com/spring-data-jpa-projections – J Asgarov Jan 31 '22 at 06:33
  • your database and microservice are in same network, right? Also 400 record is not too much to fetch. You can use index and you can also increase performance by fetching by pageable and process it. IMHO your database and microservice is not in the same local network. Also how many records do you have in the table? – Gurkan İlleez Jan 31 '22 at 06:41
  • @Gurkanİlleez, yes they are in different unix servers. I need to fetch all the records as they are used for visualisation and plotting graph. – Lolly Jan 31 '22 at 06:57
  • 2
    That API hurts my eyes, an `Optional>` 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
  • Another thing, you seem to have debug logging on, switch it off. Logging will also severely decrease performance (logging is sloooooooow). – M. Deinum Jan 31 '22 at 07:02
  • Why did you ask [the same question](https://stackoverflow.com/questions/70918314/spring-boot-jpa-taking-more-time) again? Please don't do that. – M. Deinum Jan 31 '22 at 07:08
  • You have't added your full entity, please do so. – M. Deinum Jan 31 '22 at 07:13
  • @M.Deinum Thanks for your comments. I am trying with QueryHints as you suggested. In previous post I add with Parent and Child relationship but since no luck, I made to execute two queries, fist hit parent query and get the result and feed into child query with any joins, to check if that is giving high performance. Thats the entity class, rest are setters and getters for those properties. – Lolly Jan 31 '22 at 08:58
  • I don't really understand your comment regarding the other question. As stated check the execution plan for your query as well. – M. Deinum Jan 31 '22 at 10:49

0 Answers0