Optimized JdbcPagingItemReader configuration for process indicator pattern [BATCH-2317]

Jimmy Praet opened BATCH-2317 and commented

For a new project I'm currently evaluating the performance of using JdbcPagingItemReader versus JdbcCursorItemReader.

The application will make use of a process indicator column in the input table, so saveState="false" will be configured.

In my tests the JdbcCursorItemReader is way faster (5x) than the JdbcPagingItemReader. But this is mostly due to the fact that the JdbcCursorItemReader is doing a simple "SELECT FROM

The JdbcPagingItemReader however is doing "SELECT FROM

When working with a process indicator column these " > " and "ORDER BY ASC" clauses are actually not required.

After doing some local hacking to the JdbcPagingItemReader and PagingQueryProvider to remove the sort key condition and order by clause from the query, the performance is comparable to that of JdbcCursorItemReader for this scenario.

The process indicator column is a pattern that is being promoted in the spring batch reference manual, so I think it would be nice to have both standard JDBC reader implementations support this pattern in a performant way.

No further details from BATCH-2317

Start by reading JdbcPagingItemReader and PagingQueryProvider, then trace how the paging query adds the sort-key condition and ORDER BY clauses. The issue's requested outcome is support for a process-indicator configuration that omits those clauses while preserving normal paging behavior; compare the generated queries with the examples in the report.