Will the SELECT Query be able to retrieve 200K records in Spring Batch

Question

PROBLEM:

We have a below query to be triggered in a database that may have 200K records in TABLE.

SELECT a.* from TABLE a inner join ( SELECT f.COLUMN1, f.COLUMN2, f.COLUMN3, MAX(f.DATECOLUMN4) as LATEST_DATE FROM TABLE f WHERE f.DATECOLUMN5 >= '2020-01-01 00:00:00' AND f.DATECOLUMN5 < '2020-06-04 23:00:00' AND f.COLUMN6 = true  GROUP BY f.COLUMN1, f.COLUMN2 , f.COLUMN3) b ON a.COLUMN1 = b.COLUMN1 and a.COLUMN2 = b.COLUMN2 and a.COLUMN3 = b.COLUMN3 and a.DATECOLUMN4 = b.LATEST_DATE WHERE a.DATECOLUMN5 >= '0001-01-01 00:00:00' and  a.DATECOLUMN5 < '2020-06-04 23:00:00' and a.COLUMN6 = true

Below is the Repository class that we are using to trigger the query

@Repository
public interface Repository1 extends JpaRepository<Entity1, Long> {

//@formatter:off
  @Query(value = "select a.* from TABLE a " + 
      " inner join ( " + 
      "  select f.COLUMN1, f.COLUMN2, f.COLUMN3, max(f.DATECOLUMN4) as LATEST_DATE " + 
      "      from TABLE f " + 
      "      where f.DATECOLUMN5 >= ?1 and  f.DATECOLUMN5 < ?2 and f.COLUMN6 = true " +
      "      group by f.COLUMN1, f.COLUMN2, f.COLUMN3 " + 
      " ) a " + 
      " on a.COLUMN1 = a.COLUMN1 and a.COLUMN2 = a.COLUMN2 and a.COLUMN3 = a.FDP_REQUEST_TYPE_NAME and a.DATECOLUMN4 = a.LATEST_DATE "
      + " where a.DATECOLUMN5 >= ?1 and  a.DATECOLUMN5 < ?2 and a.COLUMN6 = true ",
      nativeQuery = true)
//@formatter:on
  List<Entity1> findRecordsBetweenDATECOLUMN5(LocalDateTime startTime, LocalDateTime endTime);

}

I cant test the code as I do not have access to the TABLE which contains 200K records. So please consider the expected number of records to be retrieved is 200K. will the query be able to retrieve that many records to the List?

Please advice.

score 3 · Accepted Answer · answered Jun 05 '20 at 13:19

will the query be able to retrieve that many records to the List?

If you give your application enough memory, then yes, I don't see why It couldn't.

However, I would recommend using a paging item reader in order to load records in chunks. The whole idea of chunk-oriented processing is to read data in chunks and not load the entire data set in one go (in a List or any other data structure in memory).

Will the SELECT Query be able to retrieve 200K records in Spring Batch

1 Answers1

Linked