SELECT查询能否在Spring Batch中检索200K条记录

问题描述

问题:

我们在数据库中可能有20万条记录的数据库中触发了以下查询.

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

下面是我们用来触发查询的Repository类

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);

}

我无法测试代码，因为我无权访问包含200K条记录的表.因此，请考虑要检索的预期记录数为200K.查询将能够检索到列表中的那么多记录?

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?

请咨询.

推荐答案

如果您为应用程序提供足够的内存，那么可以，我不明白为什么它不能.

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

但是，我建议使用分页项读取器，以便以块的形式加载记录. 面向块的处理的整个想法是要分块读取数据，而不是一次加载整个数据集(在List或内存中的任何其他数据结构中).

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).

这篇关于SELECT查询能否在Spring Batch中检索200K条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！