Using Query Object to Improve NAV Performance

The aim of this blog is to introduce Query Object and to provide an overview of different scenarios where queries can be used to improve NAV performance.

Query Object type was introduced back in NAV 2013. It gives us the opportunity to define and call T-SQL SELECT statements directly from NAV. Using queries can bring many performance benefits. Despite that, they are still rarely used.

One of the most common operations in a relational database is joining two or more tables. The join operation has traditionally been done in C/AL by record looping. Queries allow us to produce a dataset that is the result of a join operation between two or more tables.

The main advantages of queries are:

• One SQL request – Queries allow NAV to retrieve all the data in one request.
• Support for FlowFields – A sub-query is automatically added to the SQL statement to retrieve each FlowField in a query.
• Totals and Group by – Queries naturally support totals and grouping of data. What has traditionally been done with temporary tables can now be replaced by simple queries. In addition, NAV Server will automatically use a SIFT indexes to optimise the calculation of totals where possible.
• Limiting dataset – Queries enable us to select only the columns we need in the result (not the entire record), we can limit the number of rows by specifying filters.

There are some useful examples of how queries can be used in NAV:

• Query as a page data source – To display the result of a query on NAV page you have to copy query result data in a temporary table, selected as a page source table. This design approach can be seen on the standard NAV Page 9126 “Lot Numbers by Bin FactBox”.
• Query as a report data source – Reports, similar to pages, cannot natively use a query as a data source. As a workaround, we must use an Integer table to loop through the query dataset and store the result in variables, which are used as a report dataset. Standard NAV Report 19 “VAT- VIES Declaration Tax Auth” is designed by following this approach.
• Query in C/AL functions – In standard NAV 2017, queries improve performance in more than 40 functions. Queries are used to calculate amounts (e.g. Customer Overdue Amounts on the Customer Statistics page), to detect duplicate records, to replace nested loops (e.g. when matching bank reconciliation lines) etc.
• Exposing data as an OData web service – You can register and publish a query as a web service in the same way that you can register and publish pages or codeunits. After you expose a query as a web service, you can import it into other applications, such as Power BI.
• Saving a query as an .xml or .csv file – You can use the SAVEASXML function to create an .xml file that contains the resulting dataset of a query. You can use the .xml file to integrate with external applications.
• Creating charts – NAV charts can be based on a query instead of a table.

A potential downside of using queries is that NAV does not do any caching for the result sets. When you run a query, NAV always gets the data directly from SQL Server. This limitation should be carefully considered when you design your solutions with queries. As a general recommendation, you should always measure the performance of your solution before and after the change. You can monitor how your application code performs with Dynamics NAV Application Profiler tool. This aside, queries are very useful and can provide many benefits.