Solved

Reconciliation, Efficient Processing Suggestion?

Posted on 2013-12-16
5
240 Views
Last Modified: 2013-12-26
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.

The scenario:

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 & " )"

Open in new window


Possible issues:
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.
Question
Will Access reuse the work table space or use new storage every time they are populated?

Question
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.
0
Comment
Question by:mlcktmguy
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't have time at the moment to think this through in detail so I'll give you some high-level ideas.
1. If you can do the update with a query, do it that way.  Queries are significantly faster than code loops when your data set is this large.
2. If you do it in code, you need to do what we old-timers call a two-file match.  You use one query that sorts the master into some predictable order and a second query to sort the detail into the same order.  You need a loop within a loop.  The outer loop reads the master and the inner loop reads the detail.  You will need variables to hold "key" values on which you do the matching.  Depending on whether the inner record is <, =, or > dictates which of the two files need to read the next record.  

Your current method is running thousands of unnecessary queries and making temp tables.  A two-file match will handle the process smoothly and in the most efficient manner since each table is read only once and it doesn't require any temp tables or additional queries.

If you don't need the individual records from the detail recordset, use a totals query to sum them so you only process one detail record per master.  It will also make the logic simpler.
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
PatHartman, thanks for your response.  I to am an old timer and ran many two file matches in the Cobol batch processing days.

The calculations needed to get the balances on the one side don't seem to make that workable in this situation.  I guess I could run thru all 300,000 records calculating the balance then do a two file match.

No fast way to do any of this.

Do you know about how Access manages the storage for temp table? To calculate the balances for all 300k records will have me creating and clearing some temp tables the same amount of times.  The temp tables for each are small but if Access doesn't reuse the space it will most definitely bloat the accdb beyond 2GB prior to completion.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Either run a query to summarize the balance and use that as one of the files or do the summarization as you pass through the file.  Reading through 300,000 records ONCE is far better than your current method of running thousands of queries.  After reading your explanation again, it looks like you have three files.  The beginning balances and transactions can be unioned to bring you down to two inputs.  You just need to create a dummy field so that the beginning balance always sorts to the beginning of the transaction set from the transaction table.

The key is to process all the inputs sequentially and that means they all need to be queries that include order by clauses.
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
I agree that calculating the balances prior to comparing the files will be more efficient.  That brings us to the two file match.  Haven't written one in over a decade, actually more like two decades, so please verify that my memory is correct and I am on the right track.

Source1:  Sorted in Order by BRT and Period
Source 2: Sorted In Order By BRT and Period

Open both files:

Source1Key = BRT & Period
Source2Key = BRT & Period

If Source1Key = Source2Key
       Compare the individual values to make sure they are the same and take whatever
        action to identify them
      Read Source1, Reset Key, If Source1 EOF then Source1Key = "99999999999999"
           In Cobol  this is where we used to use the Cobol constant HighValues to load the
            keys when the file were 'At End'.  At End Source1Key = HighValues.  Wow, this is
             taking me back.
      Read Source2  Reset Key If Source2 EOF then Source2Key = "99999999999999"
      begin compare loop again

If Source1Key > Source2Key
      ' this means that Source1Key does not have a corresponding value in Source2
     Identify it as such and take whatever action
     ReadSource2, Reload Key  If Source2 EOF then Source2Key = "99999999999999"
     begin compare loop again

If Source1Key < Source2Key
    ' this means the Source1 entry does not have a corresponding entry in Source2
    Identify it as such and take whatever action
    Read Source1, Reload Key If Source1 EOF then Source1Key = "99999999999999"
   begin compare loop again.

Do this loop until both tables are at EOF (Both Keys = "99999999999999".

I regressed back to Cobol thinking where 'GoTo's' were used more frequently.
I know there is probably a cleaner way of doing this within Access using Do or While loops but it's just not coming to me right now.

Do you have any examples coded in Access that are set up to loop appropropriately?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
I'm sure I have some but I can't lay my hands on any at the moment.
Read source1
Read source2
Fill rec keys
Do until source1.EOF
    If source2.EOF
        Exit Loop 
    End if

    If key1 = key2
        matching code
        source2.movenext
        fill key2
    Else
        If Key1 > key2
            source2.movenext
            fill key2
        Else
            source1.movenext 
            fill key1
        end if
    End if
Loop

Open in new window

The above code is quite simplistic.  It assumes that there will be 1 record in source1 for every n records in source2. It assumes you only want to process matches.  It also assumes that once either file ends, the other will be ignored and the loop will end.

There isn't really a generic routine because a lot depends on your process and what action you need to take on each situation.  You may need to raise errors if you have records in one file that don't have any match in the other or you may just ignore this situation as the above code does.

Why not start using my example and fill in as you better understand your requirements.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now