java Spring JDBCtemplate行映射器对于少量记录来说太慢

Question

我正在使用Spring和postgresql，并试图通过JDBCtemplate行映射器运行一个查询。 我的数据库中有大约100万条记录，从查询中只返回了12条记录。 当我在数据库上运行这个查询时，它需要不到一秒的时间，但是当我使用JdbcTemplate和RowMapper时，大约需要11秒才能得到结果

这是我的代码：

控制器

@RequestMapping(
      path = arrayOf("/getAppData/{userId}", "/getAppData/{userId}/"),
      method = arrayOf(RequestMethod.GET))
open fun getAppData(@PathVariable("userId")userId:Long): ResponseEntity<*> {
    var user= userRepo.findOne(userId)?: throw exception(ExceptionEvent.UserIdNotFound,userId)
var groups= groupService.loadListOfNodeIds(user.groupId)
var res= appService.getLineChartByGroupYear(userId,groups)
return ResponseEntity(res, HttpStatus.OK)

}

服务

@Service("appService")
@Transactional
open class AppServiceImpl : BaseService(), AppService {

    @Autowired
    private lateinit var jdbc: JdbcTemplate

    @Autowired
    private lateinit var complianceRepo: ComplianceRepo

    @Autowired
    open lateinit var groupService: GroupService


//**The query**


    override fun getLineChartByGroupYear(userId: Long,groups: List<Long>): List<Map<LocalDateTime,Float>>? {

        var sql= """ SELECT
                 date,
                  CASE WHEN TOTAL = 0 THEN 0 ELSE 1.0 * compliant / total end as compliance_rate
                   from
                   (select
                    date_trunc('month',location_timestamp) as date,
                    count(case when event_type = 'COMPL_HANDWASH' then 1 else null end) as compliant,
                    count(1) as total
                    from compliance
                    where group_id = any(ARRAY $groups) and location_timestamp between (now() - interval '1 year') and now()
                     group by 1) A
                     ORDER BY date; """
        var stopwatch=StopWatch()
        stopwatch.start()

        var res=jdbc.query( sql,
            BeanPropertyRowMapper<lineChartDTO>(lineChartDTO::class.java))
          stopwatch.stop()
          logger.info("LineChartByGroupYear query BeanPropertyRowMapper1: ${stopwatch.totalTimeSeconds}")
        return res

     }
}

行映射器的lineChartDto对象

public class lineChartDTO {
    private LocalDateTime date;
    private Float complianceRate;

    public lineChartDTO( LocalDateTime date, Float complianceRate) {
        this.date = date;
        this.complianceRate = complianceRate;
    }

    public lineChartDTO() {
    }

    public LocalDateTime getDate() {
        return date;
    }

    public void setDate(LocalDateTime date) {
        this.date = date;
    }

    public Float getComplianceRate() {
        return complianceRate;
    }

    public void setComplianceRate(Float complianceRate) {
        this.complianceRate = complianceRate;
    }
 }

**配置**

 @Configuration
 @EnableJpaRepositories("com.nex")
 @EnableTransactionManagement
 open class DataSourceConfig {


   val DATA_PACKAGE = "com.nex.data"
   val INTERCEPTOR_KEY = "hibernate.ejb.interceptor"


     @Value("\${spring.datasource.username:${Const.NONE}}")
     lateinit var username: String

     @Value("\${spring.datasource.password:${Const.NONE}}")
     lateinit var password: String

  @Value("\${spring.datasource.url}")
  lateinit var url: String

  @Value("\${spring.datasource.driverClassName}")
  lateinit var driverClass: String

  @Value("\${spring.datasource.sslenabled}")
   var sslEnabled: Boolean = false

    @Bean
    @Primary
    open fun dataSource(): DataSource {
    var dbUrl = url
    val builder = DataSourceBuilder.create().driverClassName(driverClass)
     if (sslEnabled) {
    val sslPath = ClassPathResource("root.crt").uri.path
    val sslParams = "ssl=true&sslcert=$sslPath&sslrootcert=$sslPath&sslmode=verify-full"
    dbUrl = "$url?$sslParams"
    }
 if (username != Const.NONE && password != Const.NONE) builder.username(username).password(password)
return builder.url(dbUrl).build()
}

 @Bean
 open fun entityManagerFactory(
  factory: EntityManagerFactoryBuilder, dataSource: DataSource,
  properties: JpaProperties): LocalContainerEntityManagerFactoryBean {
  val jpaProperties = HashMap<String, Any>()
  jpaProperties.putAll(properties.getHibernateProperties(dataSource))
  jpaProperties.put(INTERCEPTOR_KEY, hibernateInterceptor())
  return factory.dataSource(dataSource).packages(DATA_PACKAGE)
    .properties(jpaProperties as MutableMap<String, *>)
    .build()
   }

  @Bean
  open fun hibernateInterceptor(): HibernateStatisticsInterceptor {
      return HibernateStatisticsInterceptor()
  }

  @Bean
  open fun requestStatisticsInterceptor(): RequestStatisticsInterceptor {
      return RequestStatisticsInterceptor()
  }
}

每次查询只返回12条记录。 我已经试过设置fetchSize，但什么也没发生