Number of SQL statements per job launch grows with the number of past executions, and grew further in 6.0

[jjh75607]

Launching a job issues a number of SQL statements that grows with how many times the same
job instance has already been executed. This is the concern raised in #4169, but the code
that issue points at (SimpleJobRepository.createJobExecution retrieving the full execution
history) no longer exists, so I measured the current behaviour instead. It is still there on
5.2.2, and about eight times steeper on 6.0.4.

Steps to reproduce

Restart the same job instance 12 times. The step fails on every run so the instance keeps
being restarted with the same identifying parameters. Statements are counted per launch with
datasource-proxy wrapped around the H2 DataSource. The attached projects print the output
below.

Spring Batch 5.2.2:

launch | statements | BATCH_JOB_EXECUTION reads
-------|------------|--------------------------
     1 |         27 |      8
     2 |         35 |     17
     3 |         36 |     18
     4 |         37 |     19
     5 |         38 |     20
     6 |         39 |     21
     7 |         40 |     22
     8 |         41 |     23
     9 |         42 |     24
    10 |         43 |     25
    11 |         44 |     26
    12 |         45 |     27

Spring Batch 6.0.4:

launch | statements | BATCH_JOB_EXECUTION reads
-------|------------|--------------------------
     1 |         32 |     11
     2 |         50 |     21
     3 |         58 |     24
     4 |         66 |     27
     5 |         74 |     30
     6 |         82 |     33
     7 |         90 |     36
     8 |         98 |     39
     9 |        106 |     42
    10 |        114 |     45
    11 |        122 |     48
    12 |        130 |     51

From the second launch onward, each additional past execution costs one more statement on
5.2.2 and eight more on 6.0.4. The entry point is not the cause: on 6.0.4 the numbers are
identical whether the job is launched through JobOperator.start or through the deprecated
TaskExecutorJobLauncher, so the growth comes from the repository layer rather than from
the newer API.

Which statements repeat

On the twelfth launch, with 13 executions on the instance:

5.2.2
13x  SELECT JOB_EXECUTION_ID, PARAMETER_NAME, ... FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID = ?
 4x  SELECT VERSION FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID=?
 3x  SELECT SHORT_CONTEXT, SERIALIZED_CONTEXT FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID = ?

6.0.4
13x  SELECT JOB_EXECUTION_ID, PARAMETER_NAME, ... FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID = ?
13x  SELECT SHORT_CONTEXT, SERIALIZED_CONTEXT FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID = ?
13x  SELECT SHORT_CONTEXT, SERIALIZED_CONTEXT FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID = ?
12x  SELECT JI.JOB_INSTANCE_ID FROM BATCH_JOB_INSTANCE JI, BATCH_JOB_EXECUTION JE WHERE JOB_EXECUTION_ID = ? AND ...
12x  SELECT JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION FROM BATCH_JOB_INSTANCE WHERE JOB_INSTANCE_ID = ?

On both versions, every execution in the history costs one additional query for its job
parameters. On 6.0.4 the job execution context, the step execution context and the job
instance are each read once per execution as well, which is where the steeper slope comes
from.

Minimal Complete Reproducible example

spring-batch-4169-mcve.zip

Attached are two self-contained Maven projects, one per version:

mvn package exec:java -Dexec.mainClass=repro.Repro

One note on the 6.0 project: @EnableBatchProcessing alone now configures a resourceless
job repository, so the reproduction needs @EnableJdbcJobRepository as well. Without it no
SQL is issued at all, which is easy to mistake for the problem being gone.

Relation to #4169

Same underlying concern, different code. I did not comment there because that issue is
anchored to a method that has since been rewritten, and the numbers above are new. Happy for
you to link them or close whichever you prefer.

Environment

Spring Batch 5.2.2 and 6.0.4, JDK 17 (Temurin 17.0.20), H2 2.2.224 in-memory, macOS.

[jjh75607]

The extra statements come from the restart check in TaskExecutorJobLauncher.createJobExecution.
It reads the history through jobRepository.getJobExecutions(jobInstance). Since
SimpleJobRepository extends SimpleJobExplorer, that call lands on the explorer
implementation, which hydrates every execution it returns:

List<JobExecution> executions = jobExecutionDao.findJobExecutions(jobInstance);
for (JobExecution jobExecution : executions) {
    fillJobExecutionDependencies(jobExecution);
    for (StepExecution stepExecution : jobExecution.getStepExecutions()) {
        fillStepExecutionDependencies(stepExecution);
    }
}

Stacks captured at the repeated statements on the twelfth launch:

JdbcJobExecutionDao.getJobParameters <- JdbcJobExecutionDao.getJobExecution <- JdbcJobExecutionDao.findJobExecutions <- SimpleJobExplorer.getJobExecutions <- TaskExecutorJobLauncher.createJobExecution
JdbcExecutionContextDao.getExecutionContext <- SimpleJobExplorer.fillJobExecutionDependencies <- SimpleJobExplorer.getJobExecutions <- TaskExecutorJobLauncher.createJobExecution
JdbcExecutionContextDao.getExecutionContext <- SimpleJobExplorer.fillStepExecutionDependencies <- SimpleJobExplorer.getJobExecutions <- TaskExecutorJobLauncher.createJobExecution
JdbcJobInstanceDao.getJobInstance <- JdbcJobExecutionDao.getJobExecution <- JdbcJobExecutionDao.findJobExecutions <- SimpleJobExplorer.getJobExecutions <- TaskExecutorJobLauncher.createJobExecution

So every past execution also costs its job instance and both execution contexts. The loop
consuming the list only reads isRunning(), getStatus() and getJobParameters(). This is
also why both entry points give the same numbers: JobOperator delegates to the launcher.

The check does not need hydrated executions. Avoiding them would need a JobRepository
method that returns executions without their contexts. That is an API decision, so I would
rather leave it to you. I can open a PR along those lines, or take another direction if you
prefer.