Bad performance of GET_LAST_STEP_EXECUTION query on DB2 with large STEP_EXECUTION table

[jpraet]

Bug description
We are migrating from Spring Batch 3.x to 5.x and are experiencing performance issues (long delays between each step).
This is on DB2, with a quite large STEP_EXECUTION table (50 million records).
After some digging, I found this to be caused by the GET_LAST_STEP_EXECUTION query, introduced in 62a8f44.

Environment
Spring Batch 5.1.2, Java 21, DB2 v10.5.

Our DBA has not been able to find a solution to improve the performance with an additional index.
What we did find out is that, by removing the ORDER BY, we get the result (typically just a single row in the normal case) in a matter of milliseconds. But with the ORDER BY, it takes ~ 60 seconds.

So a possible workaround would be to remove the ORDER BY and perform the sorting on the java side.
That is something I am currently trying out with the approach of overriding JdbcStepExecutionDao#getLastStepExecution with a custom implementation as described here. Note that overriding JdbcStepExecutionDao#getLastStepExecution is not trivial though. Neither JdbcStepExecutionDao nor JobRepositoryFactoryBean are very inheritance-friendly.

[fmbenhassine]

Thank you for reporting this!

a possible workaround would be to remove the ORDER BY and perform the sorting on the java side.

This would revert 62a8f44, which moved the sorting logic from the java side to the database (for performance reason, see commit message and removed code in that change set).

What we can explore is adding a LIMIT 1, since we are expecting a single result. I think this could improve the performance of the last step execution retrieval. Would you be interested in exploring this? I will try on my side as well.

[jpraet]

Thank you for reporting this!

a possible workaround would be to remove the ORDER BY and perform the sorting on the java side.

This would revert 62a8f44, which moved the sorting logic from the java side to the database (for performance reason, see commit message and removed code in that change set).

It only reverts a small portion from that commit. Before that change, there was an N+1 problem. First, it queried all the job executions, and then for each job execution it queried all steps. I would not expect that moving just this ordering logic from the DB to the java side would generally hurt performance too much.

What we can explore is adding a LIMIT 1, since we are expecting a single result. I think this could improve the performance of the last step execution retrieval. Would you be interested in exploring this? I will try on my side as well.

LIMIT 1 should be TOP 1 or FETCH FIRST 1 ROW ONLY or WHERE ROWNUM <= 1 depending on the database used, so that adds some complexity.