Looking for some efficient processing ideas to run an account reconciliation . FYI, I have no control over how this information that is part of this reconciliation is stored or formatted prior to my application receiving it. My current method of performing the recocilliation works but is very, very slow.
There are two different sources feeding information into the reconciliation.
Source 1 – One Linked table “Source1”, provides a summary line for each of over 300,000 accounts.
Account Year Principal Interest Penalties
1111 2010 300 13.45 12.12
Source 2 – Two linked tables (Account balances and transaction). ‘Account Balances’ provides a beginning balance for the account. ‘Transaction’ contains any transaction that have occurred since the beginning balance. Prior to applying the transactions to the beginning balance, interest and penalties must be calculated and applied to the account. The interest and penalty amounts will vary based on the beginning balance amount and date of the beginning balance.
The actual reconciliation logic is pretty straightforward and I have routines already in place to do this. From Source 2 beginning balance, the application has to apply the appropriate penalty and interest and apply any other transactions (usually payments) to arrive at a current balance.
Compare the balance to Source 1 and note any discrepancies.
My question in how to do this as efficiently as possible since it has to be done over 300,000 times.
Currently the records from Source 1 are read one by one. For each record read from Source 1 the routine to calculate a balance from the Source 2 records, for the Source 1 account, is executed and compared to the Source 1 values.
Read Source 1 – Account (1111)
Call routine ‘GetBalance’ to calculate current balance for Account (1111)
Compare amounts and note discrepancy.
It works but it is very slow. Processing could easily take over 8 hours. I’m looking for ways to speed up the process to get a balance from Source 2 records.
Here is how it is the ‘GetBalance’ routine(Processing Source2 records) is working now.
Clear loacal table 'wkBalances'
Pull all beginning balance record from linked table ‘Account balances’ into a local table ‘wkBalances’.
Clear local table 'wkTransactions'
Pull all related transactions from linked table ‘Transactions’ into local table ‘wkTransactions’. The volume of transactions is not very great for each account, usually 5 or less.
I then have to apply the appropriate penalty and interest, updating the balance for the record in ‘wkBalances’. Then reduce the balance by the transaction amounts, again updating the balance in table ‘wkBalances’.
At the end of the routine the current balance is in table ‘wkBalances’
I use this format to pull the records into the temporary tables.
insertString = "INSERT INTO tblzTmpWk_Balance_Local SELECT * FROM tblBalance Where [Account] = " & passedAccount & _
" And ( [Active_YN] = " & cYesNum & " )"
I haven’t run the full 300K record processing yet but I am concerned that populating and clearing the local work tables that many times may cause the accdb to bloat beyond the 2GB limit.
Will Access reuse the work table space or use new storage every time they are populated?
Is there some way to use arrays when processing that will speed things up?
In addition to the specific questions I really looking for some ideas to make this process more efficient. As it stands now it works but is unacceptably slow.