Hey all, last week we had a little discussion here about keys. This concerning an adding question: In the past i had created a report which uses a huge table. Now i saw that i didn’t set the filters in the order the key is created. I changed this and hope to get a better result (the report is running currently). But: i’m using more fields with the SETFILTER-command than the key is existing of. Unfortunately i have no better key in this table. My question: what does the system do with this way of filtering ? Does it use the key and filter the field, that are not part of the key within the result of the first filter ? Or does it not use any key (so the runtime of the report is very slow) ? Stefan Weinreich Billing Analyst
quote:
Originally posted by StefanWeinreich: Hey all, My question: what does the system do with this way of filtering ? Does it use the key and filter the field, that are not part of the key within the result of the first filter ? Or does it not use any key (so the runtime of the report is very slow) ? Stefan Weinreich Billing Analyst
It will use the key. If you for example filter on the Sales Header table and you selected the primary key (Doc. Type and Doc. No.) and you additionally filter on Doc. Date it will filter the 1st 2 fields using the key. Then it will filter the selected Doc.-Date in sequential way (without any filter). The better the selectivity of the key used the greater the gain on performance.
Hello, if the report uses a table with a lot of records, and heavy filtering is done on that table by the report, it is always recommendable to set up a new key that exactly matches the fields that are filtered in the report. You can either enlarge an existing key with your required fields or set up a completly new one. The function SETCURRENTKEY searches for the first key that matches the indicated fields in the indicated order - that means enlarging an existing key (not changing the order of the key fields) will be no problem if other reports use that same key. If the order of the fields of the necessary key is different you will be better off to set up a completely new key. Obviously it is necesary to to set up the fields in the same order both in the key and the report. It does need additional database space though to store the new key (Database Information - Tables) but I think a report that needs several hours (or even 30 minutes) to be calculated is not really user-friendly. Saludos Nils Edited by - nilsm on 2002 Apr 29 16:33:29
You will find that the order you apply SETRANGE/ SETFILTER makes no difference, it is only when you call FIND or NEXT when the database is searched. What is important is the order that the key is created. You want to narrow down your query by useing a key which eliminates the most records at the start of the key. But any fields you want to have as a range of values (such as posting date) should be at the end of they key. When you get a key wrong and you see navision counting records at the bottom left, it is going through records in the current key. It counts the number of records from the last match of the filtered set to the next match in the current key. So if your key matches your filtered set it will find a match quickly. You should always tell navision which key to use as it does not do such a good job by itself. Paul Baxter
Just a quick note on Paul Baxters reply. It is correct that the Navision executable does not select the best key automatically. But the C/ODBC driver does.