SnowflakeResultSetV1 fetches data in chunks with spring JdbcTemplate. How to fetch whole data or manipulate chunks in snowflake-jdbc

Question

Using Spring

private JdbcTemplate jdbcTemplate;

RowMapperResultSetExtractor<DataPoint<Arpu>> resultSetExtractor = new RowMapperResultSetExtractor(rowMapper);

In ResultSetExtractor in extractData(ResultSet rs) Method

 @Override
    public List<T> extractData(ResultSet rs) throws SQLException {
        System.out.println("Inside result set extractor." + LocalDateTime.now());
        List<T> results = (this.rowsExpected > 0 ? new ArrayList<>(this.rowsExpected) : new ArrayList<>());
        long start = System.currentTimeMillis();
        int rowNum = 0;
        while (rs.next()) {
            results.add(this.rowMapper.mapRow(rs, rowNum++));
        }
        System.out.println("ResultSet Extractor time => "+ (System.currentTimeMillis() - start) / 1000 + "s");
        System.out.println("Total size of the results" + results.size());
        return results;
    }

I am seeing the ResultSet is the instance of SnowflakeResultSetV1 and fetching the data in chunks, which is slowing extractData() method inside the ResultSetExtractor.

How to manipulate the chunks in snowflake-jdbc or fetch the whole data-set. To improve the timing.

Thanks

Answer 1

There are 2 parameters that can be used for fetching result sets:

CLIENT_PREFETCH_THREADS - Parameter that specifies the number of threads used by the client to pre-fetch large result sets. The driver will attempt to honor the parameter value, but defines the minimum and maximum values (depending on your system’s resources) to improve performance.

CLIENT_RESULT_CHUNK_SIZE - Parameter that specifies the maximum size of each set (or chunk) of query results to download (in MB). The JDBC driver downloads query results in chunks.

Be aware that playing with these parameters can impact the memory usage.