NAV Data Upgrade command taking too much time when upgrading to BC 14

(0) Share

Report

Posted on by Bilal Haider

Hello,

I am upgrading NAV 2009-R2 to BC 14. I used the path =>> 2009-R2 -> 2013 -> 2015 -> BC14

I have successfully converted it to BC 14 and successfully synchronized with the merged tables.

Now when I start NAV data upgrade process it took around 40 hours to finish although the process successfully upgraded data but its a long time. I have done some research and found that 98 percent of this time is taken by one upgrade function that is Item table upgrade.

In DB that I am upgrading there are more than 131000 items and only one company. We know that product groups are obsoleted and they are converted to Item Categories during this process.

The machine on which I am working is 80 GB of RAM but not more than 30% of it is consumed during this process.

Are there any suggestions so that I can reduce the time that is taking on Start-NAVDataUpgrade command. Or any way so SQL can use full machine resources.

Thanks

All responses (8)

Answers (1)

Suggested answer

Bilal Haider 47 on at

Like (0)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

I was working on other tasks so this task went on backlog. But on weekend I run an iteration of upgrade.

Good thing is that I have found that it is exactly this function UpdateItemCategoryCodeOnLinesRecords that is taking 95 percent of the time. As Saurav suggested we can rewrite the function in SQL so I wrote and it runs extremely fast.

Thanks saurav and lars for helping me on this issue.
Bilal Haider 47 on at

Like (0)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Thanks Saurav and Lars.

Yes, now I am narrowing down to each function one by one and see which is taking much time.
Lars Lohndorf-Larsen on at

Like (0)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Hi,

I think you have narrowed this down very nicely, to one of these areas:

UpdateItemPicture;
UpdateItemCategoryPostingGroups;
UpdateMyItem;
UpgradeProductGroup;
UpdateBlockedFields;

Either remark them one by one, or design then save codeunit 104000 and change it to a normal codeunit, then run these functions one by one to see which one takes so long. You may have to first transfer all items into the UPGItem first.
Bilal Haider 47 on at

Like (0)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Another update:

530 items have pictures are as well that are transformed to media from blob during data upgrade process.
Bilal Haider 47 on at

Like (0)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Hi Lars,

The function is

[UpgradePerCompany] UpdateItem()

from upgrade codeunit 104000 that is taking more than 95% of the data upgrade time.
Bilal Haider 47 on at

Like (0)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Hi Saurav,

Verified the data cleanup points as you mentioned.

Will try to comment UpdateItemCategoryCodeOnLinesRecords procedure and share the results, if it affects.
Verified answer

Saurav.Dhyani 17,965 Super User 2024 Season 2 on at

Like (1)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Hi,

If its due to Product Group There can be two issues -

1. Data Cleanup-

There should not be any blank Item Category.

There should not be any Record in Product Group Table where Code is empty.

There should not be any record in Product Group Table where Item Category Code is Empty.

The Product Group Code Should be unique (There should not be two product Group Code with Same Code).

** Reason - Product Group becomes Item Category in future. If there are multiple records with same product group code it will confuse the process to update it.

2. Ledger Update.

There is a function - UpdateItemCategoryCodeOnLinesRecords which updates product group in ledger / transactional table.

You Should be able to rewrite this function in SQL and comment in Upgrade Codeunit.
Lars Lohndorf-Larsen on at

Like (1)

Report

RE: NAV Data Upgrade command taking too much time when upgrading to BC 14

Hi Bilal,

Good job for isolating the slow function to begin with. What function is it, and have you located the slow code in that function? Maybe there is a filter that just needs a better index. 131.000 items is not that many, so I would think that the code can be optimized, probably by matching filter and key better.