Usage of Keys in NAV2013 R2

Maria-SMaria-S Member Posts: 90
edited 2014-11-14 in NAV Three Tier
Hi Experts,

Rather silly question, I think, but I want to be sure.

Do I understand it correctly, that:

1) in NAV2013 we can use any sorting we want on forms or in code, it is not now depending on which keys exist in the table.

2) However, the speed of sorting will be much slower than if the NAV key exists, so you cannot just use any sorting you want, in for example, posting routine, because of performance issues.

Is my understanding correct, or I missed something?

Comments

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
  • Maria-SMaria-S Member Posts: 90
    Great! I thought as much.

    Thank you Mark.


    Actual thing is I need to apply a specific sorting in the posting, but I do not have a proper key. I need to sort the entries to apply by custom field, lets say "MyCode" along with other standard fields.

    Will it be a performance disaster if I change the SETCURRENTKEY statement from existing key
           ItemLedgEntry2.SETCURRENTKEY(
              "Item No.",Open,"Variant Code",Positive,"Location Code","Posting Date");
    

    to
    ItemLedgEntry2.SETCURRENTKEY(
              "Item No.",Open,"Variant Code",Positive,"Location Code", "MYCODE", "Posting Date");
    

    the latter key does not exist

    I have a slight hope that since the 'similar key' #-o :oops: exists, it somehow may help the situation.

    Thanks in advance!
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I would just implement it and see how it behaves in production. It is extremely hard to predict how keys behave anyway in SQL Server since there are so many fields in NAV that are optional and selectivity depends on how the system is used and setup.

    Large implementations should always allow some days for index tuning and partners should be aware of that by now.
  • krikikriki Member, Moderator Posts: 9,094
    The key would be good enough, it depends more on the filters you put on it.

    If you put a filter on new field AND you have a key that starts with that field AND your values or highly selective THEN it is possible that SQL decides to use that key instead of a key that starts with "Item No.".
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • jensthomsenjensthomsen Member Posts: 173
    But does this mean that we don't need secondary keys on tables anymore? Or my question should be: In what cases do we need to use a (secondary) key on a Nav table in Nav2013/Nav2013R2?

    If we now can use:

    Table_xxx.SETCURRENTKEY('Field1','Field2','Field3');

    Where the key 'Field1','Field2','Field3' doen't exist on Table_xxx, there will be no need to create it? Will it even decresase performance creating it on the table? The SQL server seems to be handling all the key-issues now...
  • DenSterDenSter Member Posts: 8,304
    It means that you will have to learn about how SQL Server uses indexes, and how those relate to keys in the NAV development environment :mrgreen:
  • krikikriki Member, Moderator Posts: 9,094
    DenSter wrote:
    It means that you will have to learn about how SQL Server uses indexes, and how those relate to keys in the NAV development environment :mrgreen:
    :D =D> \:D/ :thumbsup:
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Evert-JanEvert-Jan Member Posts: 36
    The last few weeks I'm programming in a 2013 R2 environment. And I discovered step by step the new things in it. I want to propose you an idea how to handle indexes in 2013 R2. Suppose a big table and I want to query on it. And I want the query to be as fast as possible because the query is used very often. Suppose the query filters on 3 fields in the table:
    Table.SETRANGE(CodeField1, FromCode1, ToCode1);
    Table.SETRANGE(CodeField2, FromCode2, ToCode2);
    Table.SETRANGE(DateField3, FromDate3, ToDate3);
    
    Normally spoken I add 1 index to the table containg Codefield1,Codefield2,Datefield3.
    However, I got a suggestion to add 3 separate indexes for those 3 fields.
    Handling short indexes gives faster results than long indexes according to my colleague.

    What do you think?
    Regards,
    Evert-Jan
  • thegunzothegunzo Member Posts: 274
    @Evert-Jan

    There is no use in adding all three indexes if you are always filtering on all three fields all the time.

    Just one index on the widest range might be the right thing to do - or come to NAVTechDays and listen to Jörg Styrk. http://dynamicsuser.net/blogs/stryk/default.aspx
    ________________________________
    Gunnar Gestsson
    Microsoft Certified IT Professional
    Dynamics NAV MVP
    http://www.dynamics.is
    http://Objects4NAV.com
  • Evert-JanEvert-Jan Member Posts: 36
    Thanx for your answer.
    Colleagues will be there... :)
    Regards,
    Evert-Jan
Sign In or Register to comment.