deusaquilus opened a new pull request, #6492: URL: https://github.com/apache/fineract/pull/6492
Loan.repaymentScheduleInstallments is a lazy @OneToMany. Two call sites pay one extra SELECT on m_loan_repayment_schedule per loan: 1. The Loan COB readers (LoanItemReader, InlineCOBLoanItemReader) load each loan with findById, and every business step that touches the schedule then loads it separately. 2. LoanRepositoryWrapper.findByClientOfficeIdsAndLoanStatus and findByGroupOfficeIdsAndLoanStatus (used by ApplyHolidaysToLoansTasklet) list the loans and then call initializeRepaymentSchedule() on each one, which is 1 + N statements. Proposed change: three LoanRepository queries that LEFT JOIN FETCH the schedule, used from the COB readers through a loadEntity() hook on AbstractLoanItemReader, and from LoanRepositoryWrapper in place of the per-loan initialisation loop. Only one collection is fetched per query. A second collection JOIN FETCH would be a cartesian product. WorkingCapitalInlineCOBLoanItemReader is left on findById. Measured on a running Fineract with EclipseLink statement logging, 3 loans, Loan COB job: 3 standalone m_loan_repayment_schedule selects as-mapped, 0 with the fetch (the schedule is a LEFT OUTER JOIN on the loan query). The other lazy collections the job touches were identical in both runs. I located the per-loan schedule loads with a JPA N+1 detector I've been building (ExoBench), which compiles a mapping and an access path and returns the SQL the ORM prepared. Fineract is one of three codebases in the write-up, and the repayment-schedule transcripts are [there](https://exobench.ai/blog/join-fetch-may-not-save-you). The numbers above come from running Fineract itself, since the point of that post is that a fix which reads correctly in the source still has to be measured. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
