Dynamics BC Online performance tuning

jordi79

In "classic" NAV when there is a performance issue, say with a big transaction table e.g. "G/L Entry", I will run a SQL database query analyser, and find out what index key fields I need to add to GLEntry table. And this normally will address the problem. Until, some other usage trend comes along. Then I will rinse and repeat.

I just realised that in Dynamics BC online, this is no longer possible. (Please correct me if I'm wrong here...). In Dynamics BC Online, you cannot added key index fields (or sum index fields) that has your extended fields along with standard NAV fields. And if this is true, this is a deal breaker for big enterprise companies planning on jumping on the dynamics bc cloud bandwagon. This is perfectly fine for small to medium companies.

jordi79

Actually, this will be addressed in next release...

https://experience.dynamics.com/ideas/idea/?ideaid=5a2cfc87-1f52-e911-b047-0003ff68d113

yzhums

Hi, this was released about half a year ago.
https://yzhums.com/10699/

Regarding Performance Toolkit:
https://yzhums.com/13940/

Hope this will help.

jordi79

hi,
But I realise that you cannot add Standardfields + ExtendedFields into the same key.

E.g.
1) I have extended GLEntry table with a new field called "NewField1"
2) I cannot add a new key to GLEntry table with ("Posting Date","NewField1")

I guess the work around is to introduce a new copy of "Posting Date" into the table extension.
e.g.

1) Extend GLEntry with 2 fields. "NewField1" and "PostingDateCopy"
2) Program in trigger so that "PostingDateCopy" is updated when GLEntry."Posting Date" is updated
3) Now we can add key with the field ("PostingDateCopy", "NewField1")

The next question would be is it worth it to do this?

DenSter

You can do a SetCurrentKey(NewField,PostingDate) without an actual index, all that will do is create an order by clause to the query.

The fields in the table extension are in a separate physical table, they don't get added to the base table. Unless the Posting Date is part of the PK, that field is no part of the extension's table, so it physically can't create that index. The link between the base table and the extension's table is through the PK fields, as far as I understand the Posting Date plays no role in getting the record from the companion table