Farooq Ayoade created FINERACT-2672:
---------------------------------------
Summary: Standing Instruction batch loads every active instruction
into memory and filters due-ness row-by-row in Java — add a persisted next-run
date, an SQL "due today" filter, and chunked processing
Key: FINERACT-2672
URL: https://issues.apache.org/jira/browse/FINERACT-2672
Project: Apache Fineract
Issue Type: Improvement
Components: System
Reporter: Farooq Ayoade
h3. Current behaviour
The scheduled job selects work with
{{{}standingInstructionReadPlatformService.retrieveAll(ACTIVE){}}}, whose query
filters only on {{{}status = ACTIVE{}}}, the
{{{}valid_from{}}}/{{{}valid_till{}}} window, and {{{}last_run_date <>
businessDate{}}}. Recurrence *due-ness is not expressed in SQL* — the entire
active set is materialised into a single in-memory {{{}Collection{}}}, and
{{ExecuteStandingInstructionsTasklet}} then calls
{{isDateFallsInSchedule(...)}} *per row in Java* to decide whether each one is
due. There is {*}no pagination, no batch limit, no chunk cursor{*}. Per due
instruction there is also an N+1 shape (an extra {{retriveLoanDuesData(...)}}
for loan targets, plus a per-SI history insert and a {{last_run_date}} update).
So cost is *O(all active SIs in the validity window)* loaded in memory,
regardless of how few are actually due today. This does not scale cleanly as
the active-SI count grows.
h3. Proposed behaviour
# *Persist a next-run date.* Add a {{next_run_date}} column, computed on
create/update and after each successful run from the recurrence rule. The job
then selects {{WHERE status = ACTIVE AND next_run_date <= businessDate AND
valid_from <= businessDate AND (valid_till IS NULL OR businessDate <
valid_till)}} — turning O(all active) into O(due today). This also lays the
groundwork for catch-up (a due date in the past is naturally selected).
# *Chunked / paged processing.* Process due instructions in bounded chunks
(Spring Batch chunk or explicit paging) so the working set is bounded
regardless of tenant size.
# *Reduce N+1.* Batch-load loan dues for the due set rather than one query per
instruction where practical.
Keep {{priority DESC}} ordering (already honored) within each chunk.
h3. Acceptance
* With 100k active SIs of which 200 are due today, the job selects ~200 rows
(verified via query plan/row count), not 100k, and never materialises the full
set in memory.
* Due-ness results are unchanged vs. the current Java filter for a
representative recurrence matrix (regression set).
* {{next_run_date}} is correctly advanced after a successful run and left in
the past after a missed/failed run (enabling catch-up work later).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
