Usage of Keys in NAV2013 R2

Maria-S

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?

Marije_Brummel

Yes, this is correct.

Maria-S

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_Brummel

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.

kriki

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.".

jensthomsen

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...

DenSter

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

kriki

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

=D> \:D/ :thumbsup:

Evert-Jan

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?

thegunzo

@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

Evert-Jan

Thanx for your answer.
Colleagues will be there...