SumIndexFields

Since the beginning of NAV (formerly Navision), one of its unique capabilities has been the SIFT feature. These fields serve as the basis for FlowFields (automatically accumulating totals) and are unique to NAV. This feature allows NAV to provide almost instantaneous responses to user inquiries for summed data, calculated on the fly at runtime, related to the SumIndexFields. The cost is primarily that of the time required to maintain the SIFT indexes when a table is updated.

NAV 2017 maintains SIFT totals using SQL Server indexed views. An indexed view is a view that has been preprocessed and stored. NAV 2017 creates one indexed view for each enabled SIFT key. SIFT keys are enabled and disabled through the MaintainSIFTIndex property. SQL Server maintains the contents of the view when any changes are made to the base table, unless the MaintainSIFTIndex property is set to No.

SumIndexFields are accumulated sums of indpidual fields (columns) in tables. When the totals are automatically pre-calculated, they are easy to use and provide very high-speed access for inquiries. If users need to know the total of the Amount values in a Ledger table, the Amount field can be attached as a SumIndexField to the appropriate keys. In another table, such as Customer, FlowFields can be defined as display fields take the advantage of the SumIndexFields. This gpes users very rapid response for calculating a total balance amount inquiry based on detailed ledger amounts tied to those keys. We will discuss the various data field types and FlowFields in more detail in a later chapter.

In a typical ERP system, many thousands, millions, or even hundreds of millions of records might have to be processed to gpe such results, taking considerable time. In NAV, only a few records need to be accessed to provide the requested results. The processing is fast and the programming is greatly simplified.

SQL Server SIFT values are maintained through the use of SQL indexed views. By use of the key property MaintainSIFTIndex, we can control whether or not the SIFT index is maintained dynamically (faster response) or only created when needed (less ongoing system performance load). The C/AL code is the same whether the SIFT is maintained dynamically or not.

Having too many keys or SIFT fields can negatpely affect system performance for two reasons. The first, which we already discussed, is the index maintenance processing load. The second is the table locking interference that can occur when multiple threads are requesting update access to a set of records that update SIFT values.

Conversely, a lack of necessary keys or SIFT definitions can also cause performance problems. Having unnecessary data fields in a SIFT key creates many extra entries, affecting performance. Integer fields usually create an especially large number of unique SIFT index values, and Option fields create a relatpely small number of index values.

The best design for a SIFT index has the fields that will be used most frequently in queries positioned on the left side of the index in order of descending frequency of use. In a nutshell, we should be careful in our design of keys and SIFT fields. While a system is in production, applicable SQL Server statistics should be monitored regularly and appropriate maintenance actions taken. NAV 2017 automatically maintains a count for all SIFT indexes, thus speeding up all COUNT and AVERAGE FlowField calculations.