Optimising Audit Command Language (ACL) Coding
Posted on July 7, 2010I 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.
Tags: ACL, Audit Command Language, CAATs, Data Analytics, Group By, Recordoffset
Categories: Audit Command Language, Data Analytics
I’ve been using ACL for the past 14 years. To be honest, I have never looked into the speed of my drive. What I can share is that I recently upgrade my laptop and is now using a i5 with 8 gig ram. I also loaded Windows 7 and is using ACL ver 9.1.8. I have seen a massive improvement going this route. Might I also suggest that you increase the buffer size in your application. It usually default to 33, but can be set to a maximum of 255. Would you mind sharing your code, there is always a way of making things faster, by changing a few things>
02.08.2011 09:34
Hi Bennie
Thanks for your reply and input. Next time I’m using ACL will def try and make the buffer changes see whether it helps.
I will try and see if I can recover some of the code from the archives when I get some time, “sanitise” it of any client info and then try and post some of the useful bits. Sharing and seeing how others do things is always a good way to learn and improve.
I haven’t checked in a while, is there any sign of a 64 bit version of ACL now ?
Justin
02.08.2011 17:48
Thanks for the much appreciated information Justin. It may be a while, but if I we do end up going with a Solid State Drive I will let you know and post impressions.
11.04.2011 18:25
Thanks, appreciate it 🙂
11.04.2011 19:24
Hi Devin
From what I have read online different SSD devices have quite different characteristics in their performance. Some are optimised for sequential read/write speeds, while others are balanced and provide better random access speeds at the cost of some sequential performance. With ACL I have always found most of the activity to be sequential in nature so look for a drive optimised that way. At the time I researched the drives the single cell drives were much more expensive than multi cell drives but had much better performance, especially when writing to areas of the disk which had been previously written to. Check this out carefully too before committing.
Look forward to your results.
Justin
07.04.2011 20:24
Hi again
Check out the benchmarks here : http://www.anandtech.com/show/4256/the-ocz-vertex-3-review-120gb/7 (and on the other pages) to get an idea of the relative speeds of drives currently on the market. The 240 gig vertex 3 really looks like the drive to beat, if the price is affordable to you.
J
07.04.2011 22:42
I will try my best to provide benchmarks. However, if I can’t since I work with ACL every day I will be able to tell you just how noticeable the difference was on a scale of (lets say) 1 to 10 or something like that. What type of reads would an SSD perform in ACL? I would assume sequential, correct?
07.04.2011 18:07
Hi Devin
I haven’t had the opportunity to try an internal SSD. I would imagine that it would be significantly quicker to use an internal SSD than 5400rpm sata. Even a 7200rpm sata gives a nice jump in speed. If you do get the chance to do some benchmarks I would appreciate some shared benchmarks 🙂
Thanks
Justin
06.04.2011 20:32
I noticed the posts and was wondering if you have been able to test ACL with an internal SSD? We currently have a SATAII 5400RPM internal laptop drive we are using and would like to know (now that SSD’s have dropped in price significantly over the past year) if this would be a justified upgrade. Thanks!
06.04.2011 20:20
Thanks for the feedback Alb.
There was a speed increase from using the SSD, not as much of an increase as I had hoped and nowhere near the theoretical maximum speed. At the time I couldn’t understand what the reason for this was. Without any evidence to support it, I think a number of things were limiting the speed.
We were using the drive as an external drive over e-sata. Is supect the bus speed was being limited on this, perhaps with a newer laptop and an internal drive the speed increase would have been better.
I have also since read (in relation to SD card speeds, and I guess the principle may also apply) that the SSD’s can be optimised for specific situations/applications. Devices with very high throughput speeds for sequential reads/writes can have much lower speeds for random access read/write.
I think that using SSD’s is def better than mechanical drives, and if I was to build a desktop workstation now to do high speed CAATS i would try my best to get a raid array of 4 drives going. Prices have fallen significantly too since the original drive we brought so this is quite a viable option. When doing caats all day long, a 50% speed increase provides a significant increase in productivity so would pay for itself quickly.
02.12.2010 21:45
Hi, Justin.
I work daily with ACL and have found your blog post very interesting. I would like to ask you just one question. Do you notice the upgrade to a SSD from a mechanical hard disk? Is it worth the upgrade?
Kind regards!
02.12.2010 19:12