Speed - Tablekey

Hi All! I’ve got one question: What is the better way to handle with tablekeys in Navision to get the fastest behaviour? Many keys with a few number of fields or only a small number of keys with many fields? Greetings Frank

Hi Frank! I think, the “construction” of a key depends on what it is used for: e.g. if you have to set many filters on a table that contains much records (e.g. T32) the key should contain (allmost)every field which is filtered, to have the filter set FAST. But this means, that this key needs a lot of disk-space, what could be a problem when restoring a database.fbk (TEMPSPACE !) Maybe it’s an idea to enable a key when it’s needed, and disable the key after use (here again: building a key needs diskspace for TEMPSPACE !) Bye, Joerg Joerg A. Stryk Apollo-Optik, IT/ERP

quote:


I think, the “construction” of a key depends on what it is used for:


I know, the “construction” of a key depends on what it is used for: But putting the right fields into a key alone doesn’t mean that data is found faster: If - for example - you take the Item Ledger Table T32 and have a key on >Item No, Posting Date, Location Code, Serial No.< This allows you to Filter on “Serial No.” but the search wouldn’t be fast. Instead you should create a key which has “Serial No.” as first key-field. It’s like with the search for telephone-Numbers: You won’t find John Smith, unless you know in which country, city he lives. The use of the right order of fields within a key is of high importance! I want to point this out because in my job as trouble-shooter for Navision I often come accross C/Side code where the programmer did not take this simple fact into consideration. The problem is that during developement phase the customer database is still so small that mistakes in the use of keys are not recognized. That comes later, if the database grows and customers have to wait 5 Minutes until Navision has sequently scanned all 2 Million Table-entries. Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch

I know that this is a very old topic but, it is so important that I am going to reply to it. The “key” if you will to keys is to eleminate the maximum number of records first. This is important in filtering and with flowfields. If you have ever seen the error message “…can not calculate the flowfield…please define a key with the following fields…the order is not important”. While technically correct, the order will greatly affect how quickly Navision calculates the flowfield. Also, if you have specifically created a new key for a flow field, make sure that there is not another key that has the same required fields higher in the order (in list of keys). Navision starts at the top and works it way down. Once it finds the FIRST key that meets the criteria, it will use that key even if it is an inferior key to another key later in the list. Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117

Hi Bill, thats really an important and interesting thing. I think that will be very helpfull!!! Thanks a lot, Frank