Today I discovered an old post on LinkedIn (https://www.linkedin.com/groups/F9-MAS90-refresh-speeds-51826.S.100899816?qid=217fadf9-d130-462a-a2f3-b447948f74bd&trk=group_search_item_list-0-b-cmr&goback=.gmr_51826) that I sent in response to a fellow group member's question about F9 and MAS90 refresh speeds. It's a highly technical discussion, so definitely not of general interest, but it's content that I want to preserve. It will be much easier for me to find it here than the haystack of LinkedIn groups. :)
F9 with MAS90 is
definitely resource-intensive. There are ways to counteract the extreme
slowness but no "magic bullet" to make it quick. Here are best
practices that I discovered.
MAS90 uses a flat-file database, so searching it will always be slow.
First you want to minimize the number of G/L accounts. Try not to add
new accounts; instead, merge or renumber as appropriate and purge (or
inactivate) unused accounts. Second, keep as few years of transaction
history in your "live" database as you need for operational/audit
reasons. Third, get into the administrative tools (after backing up, of
course) and run the routines that clean up or rebuild the database. If
you have the knowledge, get into the control file and hack out "ghost"
users. (This is an old problem, so maybe they have solved it by now.)
Your server is another potential bottleneck. If you can, run a 64-bit
OS and load it up with RAM, which is the cheapest investment you can
make for more speed. If you can't afford a dedicated accounting server,
at least make sure that it is running no other processor- or
memory-intensive tasks.
Next comes data transfer. You need a fat pipe from the server to your
desktop; i.e., gigabit ethernet. Wireless or WAN/VPN performance will
be painful. Physically sit as close as possible to your data so that
your desktop is not behind any routers or switches. IT pros may tell
you it makes no difference but you need even the slightest edge you can
get.
On your desktop, also run a 64-bit OS and load it up with RAM. Unload
any RAM hog applications (browsers can be culprits) when you are about
to crank up F9. Eliminate items from your startup tray, keeping only
what is absolutely necessary for security.
I'm sure you have already discovered to set Excel for manual recalculation.
Make Shift-F9 single sheet recalculation your best friend until it is time to
validate the entire workbook. Also set Excel to recalculate before save and save whenever you are interrupted.
I believe in modular programming for efficiency and debugging. It won't
necessarily help recalculation speed but it will help your programming
performance. Use BSPEC statements and have them refer to cells, not
have values embedded. The same applies to your GL and NGL statements.
Build the inputs outside the print range with visibility for editing and
debugging.
In general, use as few F9 statements as possible. For instance, build
variances, sub-totals and totals the old-fashioned way. Only use F9
statements to extract values, not to do calculations.
Avoid the wildcard (*). It is powerful but hungry. Instead use a
range, such as 000..999. Trim the range for efficiency while ensuring
that you capture all data.
Precision programming creates a risk of missing accounts that have been
created in error outside the defined chart of accounts ranges. You must
build in proof totals with inclusive ranges and accept the performance
hit, as financial statement accuracy is paramount. It will help if you place all your
proof totals (PT) in one sheet and recalculate it with Shift-F9 as seldom as
possible. Create a full set of check totals on that sheet that refers
to the grand totals (GT) on each sheet. It should have =GT-PT going
across and down. That way when you recalculate you can quickly see whether
it has all zeroes.
If you are in a time crunch, ideally you will save, which will force a full recalculation, just once before
you publish. Plan that event for a coffee break, phone call, etc. and
you will reduce your stress. Also save if you are interrupted so your
PC can crank away while you are attending to something else. The more robust or complex
the worksheet, the more important this is. Even if you save without
meaning to, walk away. Take a deep breath and let the PC do its thing.
You can actually use F9's resource hoggishness to make yourself more
accessible. Again, you can mitigate but not eliminate the demand it
places on your server and PC. You can ignore my earlier advice, discipline yourself to save frequently,
get out of your chair and interact with your staff. Coming in early or
waiting until after hours does speed it up a bit but doesn't do much for
your work-life balance. Always remember to get your head out of your
spreadsheets and be a well-rounded CFO for your team and the people you
serve.
