Upgrade NAV4 -> NAV2009 performance problem

Solmyr

Dear community,
I'm working on upgrade from NAV4 to NAV2009.
I'm using standard MS Upgrade Toolkit procedures and now stuck on the Upgrade NAV 2009 Step 2 codeunit.
The problem it that upgrade takes so much time that it's impossible to finish it during weekend.
Database size is about 400Gb.
I already replaced some steps with direct SQL queries that saved a lot of time.

The bottleneck function is UpdatePurchRcpt in the standard MS codeunit 104006 "Update Posted Document Links".
I have no idea how to replace it with SQL.

It loops trough all PurchRcptHeader -> PurchRcptLines, than makes some calculation and finaly modifies Item Ledger Entries and Value Entries.

The question is how to increase speed of this function? Maybe there is already exists some sql workaround...
The other idea is just skip this function from main upgrade during weekend and run this function during few next nights after "go live". But I'm not sure that it's safe.
Any help will be appreciated!

davmac1

I have been working with a similar size database in a rel 4 to 2009 conversion.
I had a number of performance issues, but this was not one of them.

Are you running this on the SQL Server? Since there is a huge amount of transaction processing, it will run much faster on the SQL Server computer.

Have you checked the SQL keys to make sure they are enabled as used for this process and in the NAV defined sequence?

This process makes active use of a temp table. Have you check task manager to make sure everything is running in physical memory?

Solmyr

davmac1, thank you for reply!

Are you running this on the SQL Server? Since there is a huge amount of transaction processing, it will run much faster on the SQL Server computer.
Yes, NAV client started directly on SQL server machine.
Have you checked the SQL keys to make sure they are enabled as used for this process and in the NAV defined sequence?
I checked keys in NAV, all of them are enabled.
This process makes active use of a temp table. Have you check task manager to make sure everything is running in physical memory?
The server has 32GB of RAM, temp table is placed on the different hard drive form the rest of the database files. The server is not using for anything except this upgrade so I hope there should be enough resources.

For no I decided to skip this step and run it later in the night time.

davmac1

the temp table in NAV runs in memory not tempdb.
Does your SQL Server have all the memory allocated to it or do you have a max memory setting that allows some RAM for other tasks?

Order of fields in the key is important as well. I have seen terrible performance when someone made the field order of a key different in SQL Server from the order defined in NAV.

Also, anytime NAV processes a large number of records individually like this process does can take a long time if there are a huge number of rows.

Solmyr

davmac1,
All memory is allocated to the SQL Server.
For now I decided to run skipped steps from MS Step 2 nightly.
I rearranged codeunit a bit, added commits, etc.
Seems that it doesn't have any critical influence on the system at all.
Many thanks for reply!