I wrote a function in the oracle database that calculates saldo and finds the date of the operation. Here’s how it works:

- He will receive
- Start subtracting the first cell of TURNOVER_DEBIT column from the vSumm and save it.
- Then, from the saved number, subtract the next cell again
- And so on until vSumm becomes less than or equal to 0.
- When vSumm <= 0 it grabes and return OPER_DAY record.

It is taking over 20 minutes. Because the average saldo records for per customer is 70-80 and It is looping for 140 000 clients.

How can I optimize my query? Any help would be much appreciated! Thank you.

create function get_date_overdue (iAccount varchar2, iSaldo number, iDate date := get_operday()) return date is version CONSTANT char(14) := '->>26112020<<-'; vDate date; vSumm number(22) := iSaldo; vSign integer := 0; begin for r in ( select --+ index_desc(s UK_SALDO_ACCOUNT_DAY) * from ibs.Saldo@iabs s where s.ACCOUNT_CODE = iAccount and s.OPER_DAY between date '2015-01-01' and iDate ) loop vSumm := vSumm - r.TURNOVER_DEBIT; if vSign = 0 and vSumm <= 0 then vDate := r.OPER_DAY; vSign := 1; end if; EXIT WHEN vSign = 1; end loop; return vDate; exception when NO_DATA_FOUND then return null; end;

## Advertisement

## Answer

You can use the single query to fetch the `OPER_DAY`

with the mentioned requirement and then return it from the function as follows:

SELECT OPER_DAY INTO vDate FROM (SELECT SUM(S.TURNOVER_DEBIT) OVER(ORDER BY OPER_DAY DESC NULLS LAST) AS SUM_TURNOVER_DEBIT, OPER_DAY FROM IBS.SALDO@IABS S WHERE S.ACCOUNT_CODE = IACCOUNT AND S.OPER_DAY BETWEEN DATE '2015-01-01' AND IDATE) WHERE SUM_TURNOVER_DEBIT >= ISALDO ORDER BY OPER_DAY DESC FETCH FIRST ROW ONLY;

Here, I have considered that you want to scan from highest `OPER_DAY`

to lowest `OPER_DAY`

to sum the `TURNOVER_DEBIT`

and once the sum of the `TURNOVER_DEBIT`

becomes equal or more than `ISALDO`

, the scan should stop and you must return that `OPER_DAY`

.

A single query can give results way faster than looping through each and every record of the table, do some arithmetic, and take decisions based on that arithmetic.

**3**People found this is helpful