I was taught that when filtering a table (for instance 39 - “Purchase Line”), one should follow a certain sequence.
Imagine that you need to filter 39 “Purchase Line” by fields “Document Type” and “Document No.” to get to the range of lines of an invoice. Following what I was taught I would start filtering from the first primary key (PK) field - “Document Type::Invoice” and then “Document No.” (the following PK field ).
Wouldn’t it perform better if we would change the order of the sequential query. This would mean writing:
I say this because when you filter Purchase Line by field “Document No.” it is most likely that you get much fewer lines than if you start by filter “Document Type”. Following this logic it would be faster to apply the remaining filter (Document Type).
Does the order of the query change the query’s performance? Is the multiple field filter divided into multiple SQL statements (query on another query) or is it converted into only on SQL statement?
The order in which you put the filters does not change the performance of that query.
The performance is determined by the existing keys (In general only “Document Type”,“Document No.” and also (not always)) the SETCURRENTKEY you use. You can create an extra key “Document No.”,“Line No.”, “Document Type” and make it the clustered index. NAV Version 5.0 has this extra key.
Navision code in this form results always in one query to get the records (Even if you put multiple SETRANGE/SETFILTER on the table):
RESET
SETCURRENTKEY (always use this and preferably use the same fields as in SETRANGE/SETFILTER if the order is not important)
SETRANGE/SETFILTER (always use SETRANGE if possible)
One good way to SQL queries is by enabling SQL profiler. You will see that queries it will lunched after you made FINDSET, FINDFIRST, etc.
And current key will be used has SQL … ORDER BY [SET CURRENT KEY].
That sounds good if you want to tune the database for increased performance.
Imagine that you get to the conclusion that you have some indexes not being used and you have been geting complaints from users saying that the Nav is very slow. What is the impact of deleting standard indexes from Nav? Lets assume that those indexes wouldn’t be needed in the future.
I don’t see any problems by disabling Indexes in Navision. One of Navision tuning tasks is related to disabling / enabling indexes.
For example in item ledger entries you have many indexes related to serial / lot you don’t use tracking I don’t see the purpose of maintaining it.
But biggest issue in performance more often is related to sumindexes than sql indexes.
If you check sumindexes formula calculation, single write in a table can generated about 20 writes to maintain sumindexes in same table.
In the context of NAV, there’s a difference between “keys” and “indexes”.
Originally NAV is an ISAM database, which means (among other thigns) that it needs a “key” to sort information. For instance, if you want to sort the Customer table by City, you have to create a “key” for it, in the table designer.
On SQL Server, by default, all “keys” are implemented as “indexes” on SQL Server. It is a well known fact that NAV tables on SQL Server are notoriously overindexed, and that most NAV implementations on SQL Server could use a good table tuning, i.e. disabling redundant “indexes”.
Now the tricky part is that you can disable a “key” in NAV, but then every object that references that sort order in any tableview property, or with the C/AL SETCURRENTKEY command, will throw a runtime error. So what the NAV team have given us is the ‘MaintainSQLIndex’ property. By default this property does not show up when you look at the key list, but you can show the column. Unchecking this property will remove the "index’ from SQL Server, but it leaves the “key” intact.
Don’t completely disable “keys”. Instead turn off the SQL Server “index”.