j-j.co.za

I was working with a colleague the other day on trying to debug some Audit Command Language (ACL) code that was doing an interest recalculation for a moderate sized dataset of some 1.7 million records. The recalculation was taking some time to perform, running at around 70 minutes for the calculation. We were getting significant errors in our results and were quite frustrated. We had taken a small sample of records (12 months for two accounts) and had done some “manual” recalculations in Excel so we could see our code was producing the same results on the test check, but we were getting much bigger differences on the full record set.

Debugging was just getting really frustrating given that it was taking over an hour after each change to see whether the difference was sufficiently reduced to give a number that wasn’t material.

After working on the code for a morning, I decided that there had to be a better way to do this, so we started doing some rough profiling of the ACL code. Looking through it, there were a number of duplicated calculations and a number of duplicated recordoffset reads. We spent some time assigning these to calculated variables to reduce the numbers of reads and recalculations. It didn’t make much difference, shaving a few minutes off the overall time.

We looked at our hardware, a Dell E6400 laptop with a high speed SSD connected via eSata. We tried running the data calculations off the lower speed internal drive, and found that it was running at the same speed, which was somewhat worrying. At this point we also realised that a simple summing of the data was taking less than 15 seconds to read all the records and produce a total, so something (other than the data reads) was causing the huge slowdown.

We started hacking our code to pieces (ignoring the interest calculation part for now) and discovered that the calculations weren’t causing much of the overhead, the only thing that reduced the times was removing the recordoffset commands. Removing these commands reduced the times by a factor of 100+.

Now we realised that the code would have to be rewritten, almost from scratch. It took about an hour to recode the calculations into Group by statements, and boy what a difference. The first run through of the new code took a mere 26 seconds. Sure the calculations were way out, but debugging code that runs so much quicker is a hang of a lot easier.

A couple of hours later and the code was now correctly calculating the interest to within materiality.

What we learned :  The recordoffset in ACL seems to be terribly slow and should be avoided for anything but the smallest datasets. Rather invest the time and write the code using the Group by commands. The speed increases are more than significant and in the longer run will reward you handsomely.

The other little catch, don’t forget that it’s not only numbers that get type cast in your initial assignment, it’s also strings. So, using  : Assign previousinterestrate=0.00000 ensures that your interest rate will use five decimals rather than chop it to an integer if you used  assign previousinterestrate=0

Assign previousaccountnumber=”12345678″ gives you an 8 digit account number whereas previousaccount=”xx” will chop it to just two even if you later assign it to fields containing 8 digit account numbers. That one had us stumped for about an hour where the interest rates and previous transaction dates weren’t resetting when the account numbers changed. After fixing our initial assignment (as above), everything worked like magic.

Hope that helps some avid ACL coders out there to do things a little more efficiently.

The work above was performed with ACL Desktop Edition Version 9.1 found here on the www.acl.com website.