A few months ago I published a blog post describing the different options for handling large datasets in Microsoft Dynamics NAV 2013 and gave a list of factors to consider when determining which option to use. One of the options discussed was creating a SQL stored procedure to perform the necessary data processing. I noted when doing this in NAV it would cause degraded system performance due to the large number of records being retrieved and processed. In this article, I will share how to harness the power of SQL to significantly shorten the time it takes to process and retrieve the data you need.
To execute T-SQL statements on the SQL Server from NAV you must relay your statements to SQL Server by either using the Microsoft ActiveX Data Objects (ADO) libraries or the .NET Framework implementation of ADO. If you are using the NAV 2009 Classic Client or an earlier version of NAV, you must use the older ADO libraries via the NAV Automation data type. But, if you are running the NAV 2009 RoleTailored Client, NAV 2013, or a newer version of NAV, then you must use the ADO.NET integration via the DotNet datatype. Since this blog post is about NAV 2013, I will demonstrate the latter.
Although you can build a T-SQL statement in a NAV text string and then send it to SQL Server for execution, most of the time you will need several statements to complete your data processing and it will be more efficient and cleaner to create a SQL stored procedure and call it from NAV passing the necessary parameters. I recommend starting with the stored procedure. Consider what you need it to accomplish, what inputs it needs from NAV, what outputs it will produce, and how it will relay those outputs back to NAV. Variables can be passed back and forth as parameters. If the stored procedure needs records from the database, give it the necessary information to retrieve the records itself – that’s what it does best. If the stored procedure produces a set of records that are to be used when execution flow returns to NAV, you can either write the records to a physical NAV table (don’t forget that NAV does not allow NULL column values) and then retrieve those records in NAV, or the stored procedure can produce a record set and NAV can read it directly without the records ever being written to a physical table. Since most NAV developers already know how to use NAV to retrieve records from tables. I will demonstrate the last option.
First, let’s review the problem we are solving. When managing a warehouse you want to minimize the amount of time and effort spent moving inventory. In the example below, we are trying to determine the items that have had the most non-document related movements in the warehouse. The stored procedure counts all of the Warehouse Entry records of type movement performed in journals over a specified period of time for each item. These represent inventory movements not related to documents. It lists the items in descending order by the total number of movement records with a minimum number of movements needed (the @CutOff parameter) for the item to make the list. We also used dynamic SQL to allow the NAV company name to be passed in and the query executed against the Warehouse Entry table in the specified company.
In NAV we use DotNet variables to establish a connection to the SQL Server, create a SQL command with parameters, and then send the command to SQL Server to be executed. You will notice that we use the ExecuteReader() method of our SQLCommand variable to execute it and prepare the result set to be read by a SQLDataReader variable. This is how we retrieve the resulting record set in lieu of writing them to a table and querying the table from within NAV. As we read the records from the SQLDataReader we insert them into a temporary Item table to be used later.
Using ADO.NET from within NAV allows us to tap into the data processing power of the SQL Server when the datasets involved are too large to be processed within NAV in a timely manner. And we eliminate the latency involved in writing the results to a table on disk by reading the dataset directly from SQL using the SQLDataReader. These techniques can produce some substantial time savings over the typical NAV, record-by-record, approach.
I'm happy to share the code with you or your team. Contact me via twitter at @npaltice or
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 firstname.lastname@example.org. By eSoftware Professionals - Food ERP Food and Beverage Software Partner, Microsoft Dynamics NAV Partner
by eSoftware Professionals