Performance: Fix O(N^2) DB write bottleneck in start_model_runs.R for modellauncher

[anushkagupta200615-jpg]

Description

Is your feature request related to a problem? Please describe.
Yes. I'm frustrated when running large model ensembles using is_modellauncher = TRUE because the workflow execution time scales poorly and creates a massive bottleneck.

Inside the primary execution loop in start_model_runs.R (around line 324), there is a nested for loop that iterates over the entire run_list and performs a database write (PEcAn.DB::stamp_finished) for every single run—every time a single job finishes. As the number of ensembles increases, this creates an $O(N^2)$ time complexity for database writes, which adds unnecessary latency and heavily bogs down the entire system.

Proposed Solution

Describe the solution you'd like
As noted in the in-line TODO in start_model_runs.R, this logic needs to be refactored to eliminate the nested loop overhead.

My preferred solution would be to update the logic so that if is_modellauncher is true, the PEcAn.DB::stamp_finished database writes are deferred until the outside of the jobids loop after all jobs are completely finished. Alternatively, we could refactor PEcAn.DB::stamp_finished to accept a vectorized list of run_list IDs to perform a single batch UPDATE query.

Alternatives Considered

Describe alternatives you've considered
An alternative is leaving the loop in place but opening asynchronous database connections so it doesn't block the main thread, though this could lead to connection pooling issues and doesn't solve the core problem of making redundant database calls.

Additional Context

File Location: base/workflow/R/start_model_runs.R

Current Code Behavior:

        # Write finish time to database
        #TODO this repeats for every run in `jobids` writing every run's time stamp every time. This actually takes quite a long time with a lot of ensembles and should either 1) not be a for loop (no `for(x in run_list)`) or 2) if `is_modellauncher`, be done outside of the jobids for loop after all jobs are finished.
        if (is_modellauncher && write) {
          for (x in run_list) {
            PEcAn.DB::stamp_finished(con = dbcon, run = x)
          }
        }