How Well Does NAV 2013 Handle Large Datasets?

NAV 2013 Database Development Part 1: Understanding Options for Large Datasets in Dynamics NAV 2013, or How well does NAV 2013 handle large datasets?

One criticism of programming in Microsoft Dynamics NAV C/AL has been that it does not process large amounts of data efficiently, resulting in degraded system performance as the number of records processed grows into the hundreds of thousands and beyond.  And if you are speaking of NAV programming just in terms of C/AL code, then I would have to agree with you…until the introduction of the Query object in Dynamics NAV 2013.  Prior to NAV 2013, if you needed to process extremely large datasets in a relatively short period of time you could do it, but you had to directly integrate SQL processing into your NAV code in the specific places where you needed to process large record sets.  This method is still valid in the appropriate circumstances, and there are limitations to what a Query can do.  So, you must be able to determine when you should use the new Query object type, and when you need to revert directly to T-SQL code.  Let’s examine the different scenarios and the available options.

When dealing with large datasets there are a few questions you must ask yourself.  Of course, the answers to these are going to be rough, ballpark figures.  First, how many records are in your initial search set?  In other words, how many records must you search through to get the records you want.  Second, do your records come from multiple tables, and if so, how many, which ones, and how many records are in each table?  Third, how many records are in your result set?  And fourth, do you need to modify the records in your result set?  The answers to the first two questions give you an idea of whether you are going to have a potential performance issue, now or later.  Keep in mind that you must consider the rate at which your datasets grow over time and answer these questions for a year, two years, five years, or even ten years from now.  The third question helps you refine your solution, and the last question is the most critical factor in determining which solution is appropriate for your situation.

Why is the question of result set modification so important?  Two reasons – some of our options are only query options (in other words, they are read-only options), and second, if the number of records to modify is large, then that will also eliminate some options.  The most restrictive scenario is when your result set is large and you must modify those records.  In such a scenario, the best option, and quite often the only real option, is to use SQL for both the query and the data manipulation.  You can create a SQL stored procedure to perform the query and data modification and call the stored procedure from NAV using the ADO.NET integration.  If you are using an older version of NAV you will have to use ADO.  Microsoft ADO, or ActiveX Data Objects, is an older technology that provides a framework and API for accessing and manipulating data in a variety of data repositories, one of which is a Microsoft SQL Server database.  Although ADO.NET is considered an evolution of that framework and API, ADO.NET is a significant rewrite and enhancement over the original and integrated into the .NET Framework.  The reason for choosing one of these options is because the SQL Server Engine is optimized for querying and manipulating large numbers of records en masse, whereas the only way to modify records in NAV is by cycling through the records and modifying them one-by-one (excluding the MODIFYALL command, which is so limited we are not considering it at this time).

Now, if you do not have to modify your result set, or your result set is small enough to modify it one record at a time, then a good option is the new Query object.  Queries are completely created within Dynamics NAV C/SIDE, so no knowledge of T-SQL is required.  They can retrieve and join records from multiple tables and order, group, aggregate, and filter, and you can specify totaling methods on fields, such as sums and averages.  They are efficient because they are always translated into a single T-SQL SELECT statement and are executed within the Microsoft SQL Server itself.  Although Queries cannot directly modify records, they can be accessed from within C/AL code, and using additional code you can retrieve and modify the records, but only one record at a time.  Queries can also be used as a source for charts, saved as XML or CSV files, and published as OData web services.

If you do not need to modify records in your result set, you can use a Query, or you can define a SQL View and link to it using the LinkedObject property in a table definition.  This is a nice option because you can use the table in much the same way you would a regular table, but it requires some knowledge of T-SQL to create the SQL view.  This allows the Microsoft SQL Server Database Engine to handle the “heavy lifting” of complex queries and large datasets and automatically provide the results to NAV for viewing.  Although it is possible to edit data in a View, you really don’t want to because the essence of a view is that you are presenting the data in a certain fashion to be viewed by the consumer.

So, as you can see, Microsoft Dynamics NAV has several options for handling large datasets requiring complex queries with and without data modification.  The trick is understanding which options are applicable to your situation.  I will present examples of each of these methods in later blog posts, starting with NAV Queries.  Stay tuned for more database development fun.

eSoftware was the first Navision (now Microsoft Dynamics NAV) partner in North America. With over 25 years expertise our team is poised to help your company succeed. Contact us at 503.608.3601 or info@esopro.com.

By eSoftware Professionals - Food ERP Food and Beverage Software Partner , Microsoft Dynamics NAV Partner