Friday, January 23, 2015
4+ reasons to upgrade your Dynamics NAV from a performance perspective
While working for Microsoft as a Senior Engineering Lead, I was leading a team that was responsible for the end-to-end performance testing of Dynamics NAV and developing performance tests / benchmarks for partners & customers, I had a unique chance to witness the continual performance improvements made to Microsoft Dynamics NAV since 2009. In this article, I will walk you through the areas where Dynamics NAV customers can reap the most significant performance improvements in Dynamics NAV by upgrading to the latest versions.
When discussing performance improvements, I am comparing the Microsoft Dynamics NAV Three-Tier Architecture with the NAV 2009 Classic Two-Tier Architecture. I refer to Dynamics NAV 2015, but the following would also apply to Dynamics NAV 2013 and Dynamics NAV 2013 R2. When comparing performance we are considering a multi-user environment.
1. Network Performance
Comparing Classic Two-Tier Architecture to NAV Three-Tier Architecture introduces another layer of network communication between the NAV Server and the SQL Server. It is important that there is a very fast (SAN) network connection between the Dynamics NAV Server computer and the SQL Server computer. Overall, network performance is improved in the NAV Three-Tier Architecture, however as there is less network traffic between the Microsoft Dynamics NAV Windows clients and the NAV Server as the business logic is executed on the Dynamics NAV Server.
Network performance is improved by the use of the Dynamics NAV Server data cache, which reduces the number of calls to the SQL Server. After one user has read a record, subsequent users who reads the same record get it from the data cache. In earlier versions of Microsoft Dynamics NAV, the data cache was isolated for each user.
The amount of network traffic between the Dynamics NAV clients and the Dynamics NAV Server depends on the type of client (Tablet, Web or Windows) and the operations performed on the client.
Reporting is an area where improvements were made to reduce the amount of data transmitted from the Dynamics NAV Server to the Dynamics NAV Windows Clients, for example: data compression, reducing the size of the standard report datasets and limiting the communications between the Dynamics NAV Server and the Printer Drivers.
The test results demonstrate that it is possible to run 500 concurrent users, each executing up to 90 transactions per hour on a single Microsoft Dynamics NAV Server instance with 16 GB of RAM and a Quad Core Processor at 2.45 GHz.
2. NAV Client Performance
When considering Client performance it is important to choose the correct Dynamics NAV Client for the task and the network environment.
While the Dynamics NAV Windows Client can be used over a Wide Area Network (WAN) it is more appropriate to use the NAV Web Client, if possible.
When using the Dynamics NAV Web Client most of the data is processed by the Dynamics NAV Web Server and there is less data sent to the Web Browser. Thus, it is important again to have a good network connection between the Dynamics NAV Web Server and the Dynamics NAV Server.
The Dynamics NAV Tablet client can perform even better than the Dynamics NAV Web Client, as the Tablet client is designed to reduce the network traffic to the Dynamics NAV Web Server.
3. NAV SQL Performance
The SQL Server interface from Dynamics NAV Server now uses ADO.NET instead of ODBC, which was used in Microsoft Dynamics NAV 2009 and earlier. There are a number of performance improvements in the NAV Server Data Access layer.
Connection Pooling
There is no longer a one-to-one correlation between the number of client connections and the number of SQL Server connections. In earlier versions of Microsoft Dynamics NAV, each SQL Server connection could consume up to 40 MB of memory.
Data Cache
The Dynamics NAV Server data cache is shared by all users who are connected to the same Microsoft Dynamics NAV Server instance. In earlier versions of Microsoft Dynamics NAV, the data cache was isolated for each user. This not only reduced network traffic, but also reduced the SQL Server workload.
Multiple Active Result Sets (MARS)
Records are now retrieved using multiple active result sets (MARS). Functions such as NEXT, FIND('-'), FIND('+'), FIND('>'), and FIND('<') are generally faster with MARS than the server cursors that earlier versions of Microsoft Dynamics NAV used.
FlowFields
In most cases, filtering on FlowFields now issues a single SQL statement. In earlier versions of Microsoft Dynamics NAV, filtering on FlowFields issued an SQL statement for each filtered FlowField and for each record in the table in order to calculate the filtered FlowFields.
NAV Query
The NAV Query object allows you to query the database to retrieve data from multiple tables in a single SQL command and join the tables in the most efficient way. Queries results are read-only and are thus faster than record based data-access. Converting C/AL code that uses nested loops to use a query can provide significant performance gains.
4. Multi-Tenancy
Multi-tenancy is a feature to enable a new business model where multiple customers share the same application code. However, there can be performance advantages for an existing solution with a large number of companies. By splitting the database containing many companies into multiple databases, it is possible to distribute the workload over multiple database servers.
Drive Performance Enhancing Upgrades
Now that you are aware of the four powerful performance improvement reasons to upgrade to newer versions of Dynamics NAV, you can effectively speak with your customers about the performance benefits of upgrading.
Author of the post: David Worthington, Dynamics NAV Performance Expert.