Hi everyone, I always wondered if it matters in which order you are setting the filters on the fields. This may sound weird but here is a test I did: Consider Customer Ledger Entry (CLE) table. The table has 718317 with the posting date range that I chose and 16 386 open entries. Common entries, if you apply both filters, 8406. Before looping through them: 1. Key used Open,Posting Date. Set first True on Open and then set the date range. The loop through all the record took 39.587, 39.026, 37.985, 38.525 secs for the 4 times I ran it. 2. Key used Posting Date,Open. Set first the date range and then True on Open . The loop through all the record took 48.109, 48.490, 52.374, 48.480 secs for the 4 times I ran it. So it looks like you should filter first on the field that reduces the most the range of data. One tip if you want to test this is that you should recompile the codeunit / report before every run because otherwise after the first run the data set is in the cache and it takes 5 secs to do it 
Any comments? P.S. One thing I remember I read about was that range filters like on date should be set last anyway so maybe my example test is not the best chosen but there is a good difference anyway …
You are quite correct with your conclusion - you should apply your filters in the order that reduces the data the most. You should also apply filters in the order the fields exist in the key. Finally (and this may be obvious), you should specify the key that best fits the filters you are going to use. Others may be able to suggest some addtional basic tips for faster filtering and processing. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner Edited by - daves on 2002 Jun 20 01:44:25
I’m not sure if this is off topic, but I do have this to say. It is not always possible to determine the key that Navision will use short of disabling keys that are to be excluded. In a recent example in the item ledger entry table (v2.5)I wanted to use only Item No and Variant Code, but this key was defined in the table after a key Item No., Variant Code, Drop Shipment,Location Code,Bin Code,Posting Date. I could not find a way to force the use of my preferred key.
quote:
Originally posted by daves: You are quite correct with your conclusion - you should apply your filters in the order that reduces the data the most. You should also apply filters in the order the fields exist in the key.
This is only relevant when you work in a form. If you are working through code with SETRANGEs and SETFILTERs followed by FIND the order in which you set the filters is irrelevant. Of course you should always use SETCURRENTKEY and only filter on fields within the key for good performance. Lars Strøm Valsted ------------------------- Why can’t programmers tell the difference between Christmas and Halloween? Because OCT(31) = DEC(25)
Hi Cristi, first to answer David West’s question about keys. All secondary keys in Navision have the primary key appended. If you wish to force the selection of a specific key, then you must specify it completely, in your case try :
setcurrentkey("Item No.","Variant Code","Entry No.");
This will work, since even though Entry No. is not specified i the key definition, Navision can still see it. This will then give you the sorting you want. Note that this is in the manuals, but you need to really read carefully to interpret it. Second to Cristi’s performance issues. The reason for the difference is not directly because of the number of records that you filter out, but due to the number of records that you skip. If you filter on a boolean, then you exactly filter on the records you want, since it is only true or false. Filter on True, and all the found records are in sequence, so are found faster. In the case of Date, you are filtering by a range, this means that the DBMS needs to scan many records to find each date. Take two scenarios: Filter Open = True Date = 02022002…05052002 Date first: Date, Open 01012002,False 01012002,False 01012002,True 02022002,False 02022002,True 02022002,True 03032002,False 03032002,True 04042002,False 04042002,True 05052002,False 05052002,True 06062002,False 06062002,True 07072002,False 07072002,True Open First Open, Date FALSE, 1012002 FALSE, 1012002 FALSE, 2022002 FALSE, 3032002 FALSE, 4042002 FALSE, 5052002 FALSE, 6062002 FALSE, 7072002 TRUE, 1012002 TRUE, 2022002 TRUE, 2022002 TRUE, 3032002 TRUE, 4042002 TRUE, 5052002 TRUE, 6062002 TRUE, 7072002 The bolded lines are the filtered ones, you can see here clearly that the DMBS must do 4 searches in the first scenario, and only 1 in the second. Always remember that in Navision you are trying to filter out the records in as FEW sequential blocks as possible. It is not important how many records you filter out, it is important how narrow the search is. If for example you were filtering item ledger entries by Document type (say) Positive and Purchase, and a specific item no., then it is faster to use the key “Item No.”,“Entry Type”. If the Item is filtered by a RANGE of items, then “Entry Type”,“Item No.” will be faster. I hope this helps. _________________________ David Singleton Navision Consultant since 1991 dmks22@comcast.net___________