multiple-keys and how they are used ?

a question how does navision handles with keys...
when i have a index containing three fields [id1,id2,id3] and i put a setrange(id1,'any'); the index would be used and when i have a setrange(id2,'lala'); it wouldn't be used cause it is not the first one in the index-row ... i don't know if i am right but in sql the keys are used in that way ... how is it in navision with multiple keys ?

*** quack ***
  • Hi,

    If id1 would the only key defined in your table than your remark is correct. Setrange(id2,'lala') will work, but it will loop through all the records to check if id2 = 'lala', and this will need quite some time. Nevertheless, defining the key as (id1,id2,id3) will sort the all the records first by id1, then by id2 and so. Therefore, within each id1 the id2 are sorted correctly regarding your setrange, which will result in a faster processing of your command. I don't know how this is solved technically, but during Navision training they advised always to activate a key that has the field you want to setrange within the first three positions of the key....


    Edited by - nilsm on 2002 Apr 16 15:07:38
  • The tip given in training is right on but, I'll take a second to explain why. Some keys in Navision are made with reports or form in mind. Let's say that we want to write a report the uses the Customer Ledger table and groups by Customer No. and we also want to specify a Posting Date range. In order for the print to work correctly, Customer No. will have to be the first field in the key even though, it would perhaps be more efficient for Posting Date to be first in the key.

    This said, for optimum performance, always try to elliminate the maximum number of records first. For instance if you want to show records from the Customer Ledger that are of Document Type = Invoice and a Posting Date between 03/01/02 thru 03/31/02, the Posting Date would be the first field in the key. Document Type has only has 7 types meaning at best you will only elliminate 1/6th of the records in the "first cut" verses a potential of perhaps 90% or greater depending on the number of transactions on your database.

    I might also note this also hold true in flowfields. You may have seen the error that says "Unable to calculate the flowfield. You must define a key containing field1, field2, field2. The order is not important. While technically true, the order of the fields has a huge impact on performance. Also keep in mind that Navision will use the first key in the list that has all of the fields that are filtered on and has the proper sumindex field. So, it's possible that Navision could use a "bad" key even though you have defined a "good" key further down the list.

    Sorry to go on and on about this but, I have seen many databases that were "slow" but, after addressing a few keys were suddenly much faster.

    Bill Benefiel
    Manager of Information Systems
    Overhead Door Company
    (317) 842-7444 ext 117

    Edited by - wbenefiel on 2002 Apr 16 15:53:45
  • so me again ... and how does a setrange-function depends on the current key ? is their a difference if i use a strange on a field which is not declared as a key; a setrange on a field which is declared as key; a setrange on a field which is part of a multiple key but their are fields declared before and behind it in the key declaration; a setrange on one or more fields which are the first fields declared in a multiple key ? or does the setrange doesn't matter about what is declared as key ... but what work does the key then really make :) ...
    for information... i am interested in this problem cause i learned a lot about hashing and trees together with indexes in my last database-lesson at the university and i think that my database could be more faster if i would know how navision handles keys and indexes...
    and it really need to be faster in our network :) ... thx for your answer ...

    *** quack ***

  • as far as i remeber in my last navision training they told us, the system automatically seraches for the first matching key.
    That means, if you use SETCURRENTKEY(field1, fild4, field3) and you have several keys beginning with this fields, the system uses the first of the possible keys.

    An example:
    you have 4 indices:
    index1: field1, field4, field3, field7, field8, field9, field77
    index2: field2
    index3: field1, field4, field3, field99, field17, field18, field19
    index4: field1, field4, field3, field6

    if you use SETCURRENTKEY(field1, field4, field3) then the system chooses index1, because its the first key which contains the fields you are looking for and the fields are in the order you set in SETCURRENTKEY.

    The performance may be bad, because index4 would be a better choice. Hence you should specify field6 too in SETCURRENTKEY, because this key consits of less fields than index1 or index3 and so it's faster.

    If you are selecting for a field that isn't part of the key the system will have to look through every record in the table, and the performance will be very bad.
    If the runtime of the report or codeunit is too bad i would recommend to build an own key for this task.

    If the key you are using contains fields you don't specify the system fills this informations with *any*.
    If you choose index4 and you use SETRANGE for field1 and field6 the key will be used as SETRANGE(field1),*any*,*any*,SETRANGE(field6), and this is done in two steps.
    First step: SETRANGE(field1), *any*, *any*, *any* and then second step: within this result *any*, *any*, *any*, SETRANGE(field6)
    (Hope this isn't explained too chaotically...)

    In this way it is important to see what William told. The first step should eliminiate as much records as possible to do step 2 with as less records as possible and to make this SETRANGE as fast as possible.

    Espacially for tables with millions of records (as we have for our phonecalls-tables), this is a benefit in runtime of minutes or (in special cases) hours !

    Stefan Weinreich
    Billing Analyst
  • thx to @ll of you for these long explanations :) ... they help very much ..

    *** quack ***
  • One more word of warning. Be espicially wary of Keys in reports. Using a bad key on an indented table that is a trasaction type (a very typical navision report) can cause all kinds of problems because the indented table is repeated over and over as the "master" table is read.

    Bill Benefiel
    Manager of Information Systems
    Overhead Door Company
    (317) 842-7444 ext 117