Optimize MongoStepExecutionDao#getLastStepExecution to filter and sort at the database level

[config25]

Problem

MongoStepExecutionDao#getLastStepExecution currently performs filtering and
sorting in Java after loading all step executions for the job instance into
memory. This scales poorly as the number of step executions per job instance
grows.

Current behavior

spring-batch-core/src/main/java/org/springframework/batch/core/repository/dao/mongodb/MongoStepExecutionDao.java
(lines 113–147):

@Nullable
@Override
public StepExecution getLastStepExecution(JobInstance jobInstance, String stepName) {
    // TODO optimize the query
    // get all step executions
    Query query = query(where("jobInstanceId").is(jobInstance.getId()));
    List<...JobExecution> jobExecutions = this.mongoOperations.find(query, ...);
    List<...StepExecution> stepExecutions = this.mongoOperations.find(
        query(where("jobExecutionId").in(jobExecutions.stream()
            .map(...::getJobExecutionId).toList())),
        ...StepExecution.class, STEP_EXECUTIONS_COLLECTION_NAME);
    // sort step executions by creation date then id (see contract) and return the last one
    Optional<...StepExecution> lastStepExecution = stepExecutions.stream()
        .filter(stepExecution -> stepExecution.getName().equals(stepName))
        .max(Comparator
            .comparing(...::getCreateTime)
            .thenComparing(...::getStepExecutionId));
    ...
}

Specifically:

1. The server returns all step executions belonging to the job instance
   (no name filter applied on the server).
2. All returned rows are held in the JVM heap.
3. The full list is filtered by stepName and fully sorted by
   (createTime, stepExecutionId) in Java.

Only a single row is ultimately returned, so the cost grows linearly with
the history of the job instance despite the semantically O(1) result.

Proposed fix

Push the stepName filter, the ordering (createTime DESC, then
stepExecutionId DESC), and the limit(1) down to MongoDB. Two reasonable
implementation options:

• A single aggregation pipeline with $lookup against the job execution
  collection, $match, $sort, $limit.
• Two queries: project only jobExecutionId from the job execution collection
  for the given jobInstanceId, then query the step execution collection with
  { jobExecutionId: { $in: [...] }, name: stepName } combined with
  .with(Sort.by(...)).limit(1).

Either approach preserves the contract in StepExecutionDao#getLastStepExecution:

Retrieve the last StepExecution for a given JobInstance ordered by
creation time and then id.

Prior art

This change aligns with two closely related efforts that have already been
applied to the JDBC and MongoDB DAOs:

• PR Improve performance of JdbcStepExecutionDao::getLastStepExecution #4798 (merged in 6.0.0-RC2) did the analogous optimization for
  JdbcStepExecutionDao#getLastStepExecution — moving the ordering back to
  the database and using setMaxRows(1).
• Issue Optimize step executions counting in MongoStepExecutionDao #5061 (opened by @fmbenhassine) applied the same
  "filter/aggregate at the database layer, not in Java" principle to
  MongoStepExecutionDao#countStepExecutions.

The MongoDB version of getLastStepExecution is the remaining gap in the
pattern, and the existing source comment (// TODO optimize the query)
explicitly flags it.

I would like to submit a PR for this, including tests verifying semantic
equivalence with the current implementation.

---

[quaff]

It may not only applied to this method, you should review others too.

[config25]

@quaff Thanks for the nudge — you're right that the pattern isn't isolated to this method. I scanned the other MongoDB DAOs and it turns out most of the sibling cases are already tracked:

• MongoJobExecutionDao#findRunningJobExecutions (N+1 per JobInstance) → MongoJobExecutionDao.findRunningJobExecutions issues N+1 MongoDB queries #5356 / PR GH-5356: Optimize findRunningJobExecutions to use batch query #5357
• MongoJobInstanceDao#getJobNames (contract violation) and #getJobInstances (in-memory pagination) → MongoJobInstanceDao: getJobNames() violates interface contract and getJobInstances() performs pagination in memory #5274 / PR Fix MongoJobInstanceDao to comply with JobInstanceDao contract #5275
• MongoStepExecutionDao#countStepExecutions → addressed by Fix stepName filtering in MongoStepExecutionDao.countStepExecutions #5221 (follow-up to Optimize step executions counting in MongoStepExecutionDao #5061)

Given that, I'd like to keep the scope of this issue focused on getLastStepExecution alone, mirroring #4798 and #5061. I'll open the PR shortly.

One case I didn't find an existing issue for: MongoExecutionContextDao#saveExecutionContexts(Collection<StepExecution>) issues one updateFirst per element in a loop — a BulkOperations batch would collapse that into a
single round-trip. Happy to file it as a separate issue if that'd be useful.

[fmbenhassine]

Thank you for raising this. In fact there was a TODO to optimise that query. I planned the improvement for the upcoming 6.0.4 release.