👀 Views: 2 💬 Answers: 1 📅 Created: 2025-06-06

java spring-batch postgresql performance Java

I'm prototyping a solution and I've been banging my head against this for hours... I'm working on a personal project and I'm currently implementing a batch processing job using Spring Batch, and I'm encountering issues with the `JdbcCursorItemReader`. I'm trying to read a large dataset from a PostgreSQL database, but it seems to be causing performance bottlenecks and occasionally throws a `QueryTimeoutException`. I've configured my job to handle chunks of 100 records at a time, but the processing time is still much longer than expected. Here’s a simplified version of my job configuration: ```java @Bean public Job importUserJob(JobBuilderFactory jobBuilderFactory, StepBuilderFactory stepBuilderFactory, DataSource dataSource) { return jobBuilderFactory.get("importUserJob") .incrementer(new RunIdIncrementer()) .flow(step1(dataSource)) .end() .build(); } @Bean public Step step1(DataSource dataSource) { return stepBuilderFactory.get("step1") .<User, User>chunk(100) .reader(reader(dataSource)) .processor(processor()) .writer(writer()) .build(); } @Bean public JdbcCursorItemReader<User> reader(DataSource dataSource) { JdbcCursorItemReader<User> reader = new JdbcCursorItemReader<>(); reader.setDataSource(dataSource); reader.setSql("SELECT id, name, email FROM users"); reader.setRowMapper(new BeanPropertyRowMapper<>(User.class)); return reader; } ``` The database connection pool is configured to allow 10 connections, but during processing, I often get the following error: `org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT id, name, email FROM users]; nested exception is org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout`. I've tried increasing the `statement_timeout` setting in PostgreSQL, but it doesn’t seem to have any impact. I've also checked the database logs, and they indicate that the queries are taking longer than expected, sometimes exceeding 30 seconds. I suspect that the way I'm using `JdbcCursorItemReader` might be inefficient for large datasets. Is there a better approach or best practice for reading large amounts of data in Spring Batch? Any insights or recommendations would be greatly appreciated! This is part of a larger web app I'm building. I'd really appreciate any guidance on this. My development environment is macOS. Any ideas what could be causing this? I've been using Java for about a year now. Could this be a known issue?