0

We have a PL/SQL procedure that calculates account balance. There a two tables, accounts and transactions. Procedure goes through all accounts and for every account runs this query in parallel! (sum of + and - transactions from some date):

SELECT sum(claim) - sum(debt)
FROM transactions
WHERE account_id = :b3
  AND currency_id = :b2
  AND account_type = 2
  AND date > :b1

There is more then million accounts and similar number of transactions so this pl/sql creates lot's of queries and takes lot's of cpu and time to finish (and kills the machine it is running on).

Is there a way to calculate this in one pass through transactions table (instead of million passes) by using sum with group by account_id and currency_id or by putting sum in associative array sum[account_id, currency_id] like you would do in awk? Will it be faster? Of course sums have to be inserted in balance table in the end.

blur
  • 189
  • 1
  • 6
  • what is mean by **in one pass instead of million pass** ? If possible share table structure of both tables. – Nagendra Nigade Apr 09 '15 at 10:14
  • one pass means with one sql, right now when this batch runs i have millions of sql queries that do the same thing - one for each account/currency combination and they kill everything on that server and on all other servers on same physical machine – blur Apr 09 '15 at 10:52

1 Answers1

1

If you just want all account_id/currency_id combinations: 

SELECT account_id, currency_id , sum(claim) - sum(debt)
FROM transactions
WHERE account_type = 2
  AND date > :b1
GROUP BY account_id, currency_id
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Great, tnx! And how would you update balances with output from this? Let's say balance is just a column in accounts table. – blur Apr 09 '15 at 10:48
  • For example in shell script i would pipe it to another script that would do update accounts set balance=3rd_column where account_id=1st_column and currency_id=2nd_column, but not sure how to do it in pl/sql. – blur Apr 09 '15 at 11:50
  • See http://stackoverflow.com/questions/7030699/oracle-sql-update-with-data-from-another-table – jva Apr 09 '15 at 22:06