Add or Modify key

andrejsm

Hi, I need new key in table G/L Entry : "Document No.,Posting Date, Source Type, Source No.".
There is one secondary key "Document No.,Posting Date" in the G/L Entry. Is it better to extend existing key with addition fields, or add new key?

kriki

First : do you need to sort on those fields or are they only because you set filter on them?
If they are only to put some extra filters, it is not even necessary to add them to the key (or make a new key).
If you put a filter on "Document No.", you already limit a lot to the records. Adding fields to the key (or a new key) slows down write-performance and adds only a little to read-performance.
Without extra fields, write-performance doesn't slow down and read-performance almost don't suffer anyway.

In case you need to sort on the new fields, you can add these fields to the existing key. This is not the case for all keys. But this key is only used in the Navigation-form, so no problem.

andrejsm

There was mistake in my description of problem, I forgot about "Document No.", the value is unknown and I can't use it for filtering purposes.
Now, i see that problem could be devided in two parts:
1) I need filter, but on key "Posting Date, Source Type, Source No.";
2) Then I need to open form with key "Document No., Posting Date".

I decide to make new key "Posting Date, Source Type, Source No."

kriki

andrejsm wrote:

There was mistake in my description of problem, I forgot about "Document No.", the value is unknown and I can't use it for filtering purposes.
Now, i see that problem could be devided in two parts:
1) I need filter, but on key "Posting Date, Source Type, Source No.";
2) Then I need to open form with key "Document No., Posting Date".

I decide to make new key "Posting Date, Source Type, Source No."

A better key would be : "Source No.,Source Type,Posting Date."
Why? For performance reasons :
1) Try to keep dates as last field in a key
2) Try to get as first field, the field that retains least fields (except ofcourse date-fields).
I explain this last one:
-There are only 5 different values for "Source Type". So a filter on it will retain still 20% (on average) of all the records.
-"Source No." : there are a lot more values for this (lets say you have 10000 different values between customers+vendors+banks+fixed assets. So on average you only have 0.01% (on average) of all the records!
-"Posting date":no good because of rule 1.

Kowa

Extending existing keys can always lead to unforeseen problems. The primary key is always added to the secondary keys to make them unique. If the key is set in a code, and a program was indended to step through the table in the indended order, adding fields to the key will change that order. This can lead to very strange results if calculations are performed. These problems are very difficult to detect if the change was not documented.