Decimal fields in keys

AntidotE

Good day comrades.
I would like to ask, maybe someone knows: is it acceptable to use decimal field(s) in keys (in primary also)? If acceptable, will it be performance-breaking or normal?
Thanks in advance for constructive answers.

rhpnt

Personally I would never do it, but hey, I've seen a lot of posts writing about "unusual" things done with NAV so what the heck, go for it!

AntidotE

Thanks for reply
Well... as soon as I finish current task, I'll make some investigation in this topic and post here
But, still expect for more activity!
Did anyone see/use such stuff? Was it good in performance with millions of records?

Belias

DISCLAIMER: this is my personal interpretation and can be wrong.
actually, nav standard uses decimal fields in some keys (also in pk): i don't think it's a good practice, but as rhpnt said, sometimes is just needed!
i would care about the correct fillfactor to use, bacause a decimal field can be inserted almost anywhere in the index, thus i would select a low fillfactor (don't ask me how much), especially if the decimal field is one of the first fields in the key.

As a rule of thumb, if you go for it (and if it's possible for your businnes logic) you should put that decimal field as one of the last fields in the key.

AntidotE

Thanks for reply.

Customer wanted primary key like this:

"Metal Sheet Group" Code20
"Sheet Thickness Min" Decimal
"Sheet Thickness Max" Decimal

I told him that he will get performance down, proposed and implemented solution with the following key:

"Code" Code20

This field has automatic increment, it is unique and unchangeable for user.
Other fields, proposed by customer, are implemented, but are not in key.

now I start creating simple table with customer supposed key, fill it with millions of random data and test access performance.

AntidotE

hmm... well, with 2 decimals in key even with searching among only 500'000 records it takes around 30 seconds on my computer.
with only one code20 in key i get ~15 seconds...
so, performance is real

Belias

wait, wait...do you have sql or native?
(i supposed sql)15 secs for 500000 records seems too much for me...

AntidotE

I have not so powerful computer

Belias

sql or native?
what field are you searching?one of the fields in the key or something else?

rhpnt

Couldn't help myself not to follow the thread.

I believe that the table you are trying to put that weird key in is a "combination" table combining the metal group with different thicknesses which has to be unique!?

The solution without using the "unusual" key would be that you check the unique thickness by code. This is very simple, just put the checking routine in the onInsert and onModify trigger of the given table and voila you've created a check constraint.

AntidotE

2Belias: sql so far. looking for random value from specified range by pk.
2rhpnt: I did something like that