[
https://issues.apache.org/jira/browse/FINERACT-2672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18096449#comment-18096449
]
Adam Saghy commented on FINERACT-2672:
--------------------------------------
[~oluexpert] I like the idea. Would you mind to move this whole idea and
conversation to the Fineract DEV mail list? It might be interesting
conversation and we can finalize the appropriate design over there.
> 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
> Priority: Major
> Labels: beginner, beginner-friendly
>
> 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)
