Order of key fields

ta5

Hello

While creating a new key on a entry table with tons of record the following question occured to me:

An export neeeds to key to export the records to somewhere else, based on these 3 criterias:
1: Document Type (Option Field)
2: Document No. (Code Field)
3: Processed (Boolean Field)
DB is the native one!

In my opinion the navision way is to make the key in the order 1,2,3. But from a performance point of view I guess "Processed" should be the first field in the key, because it is the most selective field, having tons of records in the table but anyway the export will find only a few hundreds records at one time and then sets the "Processed" field to true.

Additional question: If the DB is sql server, is there a difference regarding the proper usage of such a key?

I'm looking forward to some opinions.

Thank you in advance
Thomas

David_Singleton

it depends on the filter you put on document no. If you do a SETRANGE("Document No.",DocNo); Then actually document number is the most selective key and should be first. If you filter by a range or wild card, then the others may be more selective.

The thing is that with a dynamic cursor, you are basically emulating Navision logic, so SQL is not building a proper execution plan based on statistics, its just "doing as its told" so it all a bit hyperthetical.

matttrax

I might be wrong here, but doesn't the data get sorted by the Primary Key fields after the Secondary Key fields.

So if Primary Key is Document Type, Document No, then the secondary key of Processed would be sorted by Processed, Document Type, Document No.

To sum it up: Don't you just need a key with the Processed field?

DenSter

ta5 wrote:

DB is the native one!

On native, selectivity is not really a factor, the order of the fields doesn't really matter.

David_Singleton

matttrax wrote:

I might be wrong here, but doesn't the data get sorted by the Primary Key fields after the Secondary Key fields.

ta5 wrote:

While creating a new key on a entry table with tons of record the following question occured to me:

On an entry table the primary key will be "Entry No." an integer. 8)

David_Singleton

DenSter wrote:

ta5 wrote:

DB is the native one!

On native, selectivity is not really a factor, the order of the fields doesn't really matter.

it really depends on the filter applied to "Document No." if its a setrange, then correct the selectivity does not matter, but if its a ranged filter or * or | then the order makes a huge difference.

DenSter

My "Doesn't really matter" comment applies to a situation where you have an existing key, you have existing code that uses that key, and you are filtering on those fields. It doesn't matter if the fields in the key are 1, 2, 3 or 3, 2, 1. Of course, as soon as you have a key 1, 2, 3 and you only filter on field 3, or if you filter on fields that are not part of the key, or you use wildcards, that's going to make a difference. BUT that doesn't have anything to do with selectivity.

ta5

Thanks for your valuable comments.
I'll set the thread to solved.
Regards
Thomas