Decimal fields in keys

AntidotE
AntidotE Member Posts: 61
edited 2011-02-16 in NAV/Navision Classic Client
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.
It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.

Comments

  • rhpnt
    rhpnt Member Posts: 688
    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! :wink:
  • AntidotE
    AntidotE Member Posts: 61
    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?
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • Belias
    Belias Member Posts: 2,998
    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.
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • AntidotE
    AntidotE Member Posts: 61
    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. :)
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • AntidotE
    AntidotE Member Posts: 61
    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 :)
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • Belias
    Belias Member Posts: 2,998
    wait, wait...do you have sql or native?
    (i supposed sql)15 secs for 500000 records seems too much for me...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • AntidotE
    AntidotE Member Posts: 61
    I have not so powerful computer :)
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • Belias
    Belias Member Posts: 2,998
    sql or native?
    what field are you searching?one of the fields in the key or something else?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • rhpnt
    rhpnt Member Posts: 688
    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
    AntidotE Member Posts: 61
    2Belias: sql so far. looking for random value from specified range by pk.
    2rhpnt: I did something like that :)
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.

Categories