'The system’s gone slow!’ - a phrase as old as the IT industry. As soon as humans started interacting with their computer systems, an expectation of speed was set.
Looking back 30 years or more, to my early IT career, and assessment of the performance of our mainframe solution was based on how long it took to run a batch job. Payroll could run for many hours and end-of-month updates most of the day. ‘Has my job finished yet’ was the refrain of the of the users that this young ‘computer operator' serviced.
As the years have rolled-by user expectation has increased in line with the power of software and hardware. Software is doing much, much, more as hardware provides new capabilities. This has meant that batch jobs still take ‘too long’ in some cases, both because the batch job is doing an amazing amount of work and also because…well...how long is ‘too long’?
I have also experienced the opposite of the ‘too long’ user expectation. I have peered over the shoulder of a user as their system took a minute to find a record or took 5 minutes to post an invoice, and when I expressed my horror, I was reassured with ‘oh it always takes that long’ and ‘well it is a big invoice’. Too often poor system performance is simply expected, tolerated and even a welcome break to make a cup of tea ‘while I wait for the process to finish’.
What should you look for if your system solution has hit a performance issue?
So when is ‘poor performance’ (and I am speaking of all software systems here), really poor or an unreasonable user expectation? One good starting point is whether the system is getting slower over time, i.e. a measurable slowing down of a report or batch process, slowing down of searching or data entry. Often people will excuse this ‘well there is a lot more information in the database now’ but this should not be excused. Databases are indexed, and with well-indexed systems large volumes of data should still provide a good response. The next question is whether the ‘poor performance’ is system-wide or whether there are individual processes or screen enquiries that cause the concern. In this latter case investigation will invariably find that there is a need to use improved indexing to locate information or that too much information is being read or updated. Obviously it may just be that the process takes the ‘correct’ amount of time but this doesn’t match the expectation of the user!
Too often when a performance issue arises, the default reaction is to reach out for a technical rocket scientist to analyse SQL logs or assess server statistics. So what should you look for if your system solution has hit a performance issue? Here I’ll take you through the top things to check but before diving into techy stuff you should definitely first ask ‘What else are we using the system for?’ Try and identify the ‘What’s happening?’ before trying to figure out the ‘Why’. If you have other things on the server, try stopping them. A classic issue is having anti-virus software checking files created by Dynamics NAV and SQL server! If integrations processes are being used, try stopping or moving these to another separate Dynamics NAV service to see if performance changes on the users’ service.
Also, don’t be afraid to find out the ‘Who’. If something isn’t working, is it only the case for a specific user? Try to identify whether it works for the user if they use another machine. Is it only at a certain time of the day for the user – it could be that someone on their part of the network is downloading huge media files at this time of the day! So, let's take a look at specific things to look for when assessing system performance...
1) Slow lists or cards
Does the issue appear mainly in one or two screens enquiries/updates?
If so, are there a number of calculated fields on the screen? (In Dynamics NAV these are referred to as ‘Flowfields’). A poor performing calculated field can dramatically affect the performance of a page, especially on a list where the field is being calculated for each record in the list and mainly for records you are not interested in, if you are paging through looking for a single record.
What to check
Try removing the field(s) from the page and see if the speed improves, (it’s useful to save the original page / form to a backup object number before you start playing - just in case!) If removing a calculated field makes a significant difference, there are a couple of things to do. The calculated field can be sped up by adding a correct / better ‘key’ to the underlying record (i.e. the information that the field is summing up). Next, you can simply decide that the field is not required on the list and instead move to the Card Page / Form so that it is only calculated for the record you are using. Finally, maybe you don’t need the field at all and can remove from the screens entirely!
2) Posting Performance
Is the issue related to posting?
Locking is often a necessary design feature of an application. Records are sometime required to be locked whilst information is calculated or updated to ensure a consistent set of information. Older versions (pre 2013) of Dynamics NAV locked perhaps more than was ‘necessary’. This locking is often blamed for the performance. Whilst it is certainly contributory, it is sometimes simply the ‘end result’ i.e. the symptom not the cause. For many years we have been using ‘Posting Queues’ as a way to maximise the speed of posting, to do this the order / invoice or other record is simply marked as ‘ready to post’ and behind the scenes a separate automated task deals with the posting. Automated batch queues don’t complain! They aren’t waiting to go home and are happy to wait a while if necessary. More importantly an automated queue can be run on a good machine, with maximum network performance (physically local to the main server) and have plenty of memory thus keeping as many objects as possible in memory.
What to check
Batch posting queues have been standard in Dynamics NAV since version 2013. If you are on 2013 and not using batch queues, this could be a great thing to investigate. Interestingly many ‘High volume’ ERP systems like SAP / R3 and Oracle use exactly this mechanism to separate posting activity from the user experience. After all, once the user doesn’t have to wait, they don’t perceive an issue.
3) Report Server
Another very common way to reduce the workload of your ERP solution is to get another system to do some of the work. One great example is to move the servicing of reporting information to another server / database. Reporting can put an unnecessary load on the day-to-day activity of any solution especially as users may have created ‘inefficient’ reports using end-user tools such as Jet Reports which then cause the SQL to be unnecessarily burdened.
What to check
Adding a Business Intelligence / report environment, for example Jet Enterprise (which provides a data warehouse and OLAP cube for Dynamics NAV), or simply copying the database each day to provide a snap shot of your data ‘as of last night’ is a very sensible strategy to improve both reporting needs and the needs of day-to-day users of your ERP solution.
Tools for the Job
When all is said-and-done, you may simply need to know more about what is happening and where. Tools such as ‘code coverage’ and ‘SQL Activity Monitor’ allow your reseller to analyse your Dynamics NAV system, in great detail. As such they can see how long each SQL statement takes to process, what code is running when and how efficiently.
It's in the Code
Despite all of the complexity and sophistication of SQL, infrastructure and technical analysis, sometimes the performance is just ‘in the code’ or the design of bespoke modification. To some degree this is often the easiest and most beneficial area to tackle. A badly written report may run through thousands of unnecessary records instead of filtering out the not required information, it may even modify the unnecessary records even when not required (e.g. calculating and setting a field even though it hasn’t changed since last time) causing a dramatically higher amount of write activity that needed. Humans write code and humans are fallible. If you have a particularly poorly performing process, report or screen get it checked out!
Dynamics NAV can be Huge!
As a final note here, it’s worth considering how big a Dynamics NAV solution can be and asking what can it cope with. Over the last 20 years we have implemented many very large, in fact huge, solutions on Dynamics NAV. We’ve had customers pushing thousands of orders a day into Dynamics NAV, customers with over 1,000 online concurrent users of a central Dynamics NAV solution and customers with hundreds of Dynamics NAV solutions sending data back into a central database holding well-over a Terabyte of data! Large systems need to be thought about, regardless of the software and many of the techniques are common to scaling a solution. Dynamics NAV is no exception; it can deliver amazing results when setup well. We’ve done it and can vouch for it!
If you're interested in upgrading Dynamics NAV to Dynamics 365 Business Central, read our upgrading guide here or discover more about Business Central as an excellent ERP solution here.
.png)