0

Below Employee table(Oracle DB) has 5000 active employees. From SQL client,query takes time in ms but same query when being invoked from service class as below takes around 2 minutes.

There is no association from employee entity to another entity. So not an issue of EAGER fetching.

As suggested here tried all argument constructor but it did not help.

Employee class has around 25 attributes.

Is the time spent in converting result set into entity object?

  employeeRepo.findAllByStatus("active")

Code:

 class EmployeeRepository extends JpaRepository<Employee,Long>{

           List<Employee> findAllByStatus(String status);
  }     

  @Data
  @Entity
  @Table(name="emp_master")
  @NoArgsConstrucor
  class Employee{

     @Id
      private Long id;

      @Coulmn(name="status")
      private String status;

      @Column(name="LEAVER_DATE")
      @Convert(DateToLocalDateTimeConverter.class)
      private ZonedDateTime leaverDate;

      few string and Integer varibales...
  }

DateToLocalDateTimeConverter converts sql timestamp to ZonedDateTime

jena84
  • 311
  • 1
  • 3
  • 20
  • what is Structure of Employee Entity – Shailesh Chandra Nov 11 '19 at 17:02
  • Added Employee entity – jena84 Nov 11 '19 at 17:21
  • I was wondering if you have one to many relationships in the entity , which might casing same, can you set **jap.show-sql=true** and find out SQL used by JPA, and execute same on console, and let us know the timing – Shailesh Chandra Nov 11 '19 at 17:36
  • Tried .show-sql=true it prints single select,as already in my question,not used any relationships – jena84 Nov 11 '19 at 17:44
  • just for debugging use Pageable/PageRequest and fetch first 100 records and gradually increase the size of the fetch until you start seeing degradation in performance, it could be possible that your JVM is heavy on memory when these 5000 records are in memory – Shailesh Chandra Nov 11 '19 at 17:51
  • *From SQL client,query takes time in ms but same query when being invoked from service class as below takes around 2 minutes.* Is it really the same query or having LIMIT/OFFSET set (you dont have to even know that it is done for you in some clients) – Antoniossss Nov 11 '19 at 17:52
  • @shailendra,will try ,yep Antoniosss its the same query w/o LIMIT/OFFSET – jena84 Nov 11 '19 at 17:54
  • Is the time measure in the SQL Client measured until the first result is available? Or until all results are fetched, i.e. do you scroll down to the end of results? If not, do so and measure again. – Jens Schauder Nov 12 '19 at 05:56
  • Use a profiler to see which method calls use up all the time. – Jens Schauder Nov 12 '19 at 05:57
  • @jena84 but are yo 100% sure? I am asking because for example in MySQL Workbench limit/offset is added transparently without user knowledge. It is not present in editor nor console by any means but still it is applied unded the hood. So in general native sql returns how many rows in your case? – Antoniossss Nov 12 '19 at 07:25
  • @JensSchauder i think OP got transparent pagination in his client. – Antoniossss Nov 12 '19 at 07:25
  • @Jens,Client is SQL developer,on scrolling time did not change and Oracle is not that slow to fetch 5000 records with simple select query,in code I tried pagination,with 100 records it takes 2 secs and with 1000 records it took 14 secs,I will probably use this approach now.Even though not fully satisfied will go with this approach. Thanks Shailesh and other's for your suggestions – jena84 Nov 14 '19 at 02:56

0 Answers0