Selecting the Key Search Algorithm
NOTE: This topic is for advanced users who have a strong understanding of Dynamics NAV and the impact that keys can have on performance.
Jet Professional selects the key to apply by analyzing the filters to be set on the table against the available list of keys. While the default algorithm is designed to select the key that performs best in the majority of cases, there are situations in which a different key may perform better. Depending on the data in the table, it may be beneficial to use a different algorithm to select the key.
There are now two different key search algorithms available in Jet PRofessional. The algorithm can be selected in the Advanced section in the Data Source Settings. This setting will apply to all queries that are run using the associated data source.
Figure 1: Example Table Keys
Key1: Field1, Field4
Key2: Field3, Field1, Field2
Key3: Field3, Field4, Field1, Field2
ALGORITHM #1: LEFTMOST FIELDS (DEFAULT)
This is the default key search algorithm used in Jet Reports. Under this condition, the key with the most filter fields farthest left is selected. Consider the following filter sets given the keys in Figure 1:
Key1 is selected because it contains a filter field (Field1) in the leftmost position, even though Key2 and Key3 contain more filter fields than Key1.
Key2 is selected because it not only contains a filter field (Field3) in the leftmost position, but also has a filter field (Field1) in position 2.
ALGORITHM #2: MOST MATCHING FIELDS
Using this algorithm, the key with the most filter fields is selected. If two keys contain an equal number of filter fields, they are then scored based on the number of fields farthest left. Consider the following filter sets given the keys in Figure 1:
Key2 is selected because it contains both filter fields, and they are further left than in Key3
Field1, Field2, Field4:
Key3 is selected because it contains more filter fields than any other key in the table, which is scored the highest using this algorithm.
Note that the order in which the fields are specified in the Jet Reports query does not affect the outcomes outlined in the above scenarios. In addition, if two keys are found to be equivalent based on the scoring of the respective algorithm, the key that is listed first will be used.