0

I have one table with 3524976 records. I don’t know why but when I am executing a search that the output will be one record (AKA findBySerialNo) the search will be fast.

Still, if the output is big, let's say 60000 ~ records (findByPaMode) then the process gets stuck. I assume that I probably missing some configuration.

 @RepositoryRestResource(collectionResourceRel = "tch-device-data", path = 
     "tch-device-data")
 public interface TchDeviceDataStatusNewRepository extends JpaRepository 
<TchDeviceDataStatusNew, String> {

deviceModel);
List<TchDeviceDataStatusNew> findBySerialNo(@Param("serialNo") String serialNo);
List<TchDeviceDataStatusNew> findByPaMode(@Param("paMode") String paMode);
}


 spring.datasource.url=......
 spring.datasource.username=.......
 spring.datasource.password=.......
 spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
 spring.main.allow-bean-definition-overriding=true
 spring.data.rest.basePath=/api   
 spring.jpa.show-sql=true
 spring.jpa.properties.hibernate.format_sql=true

 spring.jpa.properties.hibernate.dialect = 
 org.hibernate.dialect.Oracle12cDialect
 spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect

Any suggestions on how to handle big outputs?

Thank you

angus
  • 3,210
  • 10
  • 41
  • 71
  • What exactly do you mean by 'stuck'? What are the symptoms (the query taking a long time, increased memory footprint, ...)? – crizzis Oct 27 '19 at 09:16
  • If the result size/in-memory processing is the culprit, I'd try [switching to using `Stream`](https://www.baeldung.com/spring-data-java-8) as the result type. Also, make sure you absolutely need *all of the data* your query is loading, maybe resort to using [projections](https://www.baeldung.com/spring-data-jpa-projections) if possible. Double check you're not facing some of the [common performance issues](https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping) – crizzis Oct 27 '19 at 09:17
  • Thank you crizzis for the info. I think Stream will be the solution. Since I am implementing the method in @RepositoryRestResource (Stream<.....> findByPaMode(@Param("paMode") String paMode) where should I close the Stream ? – angus Oct 27 '19 at 14:15
  • Sorry, I totally missed the fact you're using `@RepositoryRestResource`. I'm not sure `Stream`s are supported in such a scenario. You should probably consider the other options I mentioned – crizzis Oct 27 '19 at 19:29
  • Thank you. Using Page help me. – angus Oct 27 '19 at 23:40

0 Answers0