Query Objects and Performance
A query is a new object in Microsoft Dynamics NAV 2018 that you use to specify a set of data that you want to read from the Dynamics NAV database. You can query the database to retrieve one or more fields from a single table or multiple tables. You can specify how to join the tables in the query. You can specify totaling methods on fields, such as sums and averages. This topic describes how to design queries and table keys in the most efficient way.
FlowFields in Queries
A sub-query is automatically added to the SQL statement to retrieve each FlowField in a query. This allows Dynamics NAV to retrieve all the data in one request.
Important
You cannot use a FlowField on a virtual table in a query because this cannot be converted automatically into a SQL statement.
Covering Indexes
When you use a query to select a subset of fields in a table, you should consider taking advantage of the covering index strategy. A covering index is the index that contains all output fields required by the operation performed on that index. A covering index data access strategy can greatly improve performance because the database must retrieve only data from the index instead of finding data by using the index and then retrieving the data in the clustered index. A covering index data access strategy can be used when the following conditions are true:
All columns in a given data item are part of a single Dynamics NAV key.
All columns that are used in the DataItem table filters are part of the same Dynamics NAV key.
If two DataItems are linked, then the field on the parent DataItem that links the two DataItems (the Reference Field on the DataItemLink property), must be part of the same Dynamics NAV key as the columns in the child DataItem.
The SQL Server optimizer automatically chooses a covering index strategy whenever possible.
For more information about SQL Server covering indexes, see SQL Server Optimization.
For more information about SQL Server clustered and non-clustered indexes, see Types of Indexes.
Covering SIFT Indexes
Similar to how indexes can be used to retrieve data for a query, SIFT indexes can be used to retrieve data for a query that contains totals. SIFT totals are maintained after each insert, modify, or delete call, and so some or all of the totals are already calculated. A SIFT index can be used when the following conditions are true:
The query contains at least one aggregated column with Method Type set to Totals and with Method set to either Sum, Count, or Average.
If a DataItem contains an aggregated column, then all columns under that DataItem must be aggregated columns, must use either the Sum, Count, or Average method, and must be part of a SumIndexField defined on a single Dynamics NAV key.
In a query in which you have aggregations but not on all DataItems, then for the DataItems without aggregations, the columns are part of a SumIndexField.
All non-aggregated columns under the DataItem that have aggregation are part of the key fields defined for the same SIFT index.
All columns that are used in the DataItem table filters are part of the same Dynamics NAV key.
If two DataItems are linked, then the field on the parent DataItem that links the two DataItems (the Reference Field in the DataItemLink property) must be part of the same Dynamics NAV key as the columns in the child DataItem.
Microsoft Dynamics NAV Server automatically use a SIFT index for query objects whenever possible.
Differences Between Query and Record Result Sets
Dynamics NAV does not do any caching for query result sets. When you run a query, Dynamics NAV always gets the data directly from SQL Server.
Query result sets are not guaranteed to be dynamic, whereas record result sets are always dynamic. This means that if you insert or modify data in result set row that you have not yet looped through, then it is not guaranteed that the query result set includes those changes.
See Also
Queries
SumIndexField Technology (SIFT)
Optimizing SQL Server Performance with Microsoft Dynamics NAV