Boosting performance while running upgrade toolkit on Dynamics NAV, sql server option

Upgrading Dynamics NAV (database upgrade) is a timely task, but the time to upgrade (transfer data) can be somewhat reduced on sql option by taking few simple steps.

Due to large transaction volume and amount of indexes and sifts, transfering data (running upgrade toolkit codeunits: step 1 and 2 ) can be a long process.To reduce this time, it might be beneficial to disable maintaining sql index (and maintaining sift index) for all but primary and clustered keys, and keys used by upgrade toolkit. Following procedure might help boosting performance while transfering data. 

1. As a first step, table objects should be exported so the index structure can be reimplemented when upgrade is finished. Note, in 5.0 SP1 this will cause rebuild of indexed views and might take some time on large systems. In 5.0 SP1, this should be done outside office hours. 

2. Search the objects used by upgrade toolkit for all occurences of SETCURRENTKEY in code. Note down tables and keys where SETCURRENTKEY is used. Normally, there are only a few keys (other then primary keys) used in upgrade toolkit. These keys and primary keys (also keys with clustered indexes) should be left with maintainsqlindex set to yes. For all others, this property can be unchecked.

3. Then manually (or by automatizing the process) uncheck MaintainSlqINdex and Maintain SIFT Index for all but primary(clustered) keys and keys noted down in step 2.  This might take considerable time on large systems as it will drop most indexes and sift tables, but it could be run as an unmonitored overnight process.

The following example illustrates how the process could be automatized. You might need a solution developer granule for this scenario.

Create a tabular form with source table = 2000000043 (Key), and add columns Table No., No., MaintainSQLIndex, MaintainSIFTIndex.

In OnOpenForm trigger of the form add following code:


This will show all keys except of primary and clustered keys

Add the button to the form and in OnPush trigger add the following code:


and save the form.

When running form, select all indexes you want to have MaintainSQLIndex and MaintainSIFTIndex property set to false (as an example, select all and then unselect indexes used by upgrade tollkit, collected in step 2). From menu select Edit and Toggle Mark. This will mark all selected records. Click on the created button to set the properties MaintainSQLIndex and MaintainSIFTIndex of the record selection to FALSE.

4. Run the codeunits in upgrade toolkit. Remember to recompile upgrade toolkit objects before upgrading. As only primary keys and keys used by upgrade toolkit are now maintained, running the codeunits for data transfer should now run faster.

5. When data transfer is finished, reimport table objects exported in step 1 to restore original index structure . This will rebuild indexes and sift tables, and might take considerable time, but can be run as an unmonitored overnight process.

Note that the process might be further optimized by modifying some of the keys used in upgrade toolkit. Instead of using best matching keys among existing ones (as done in some triggers), one could create keys that match the filtering exactly, and remove them after the upgrade process is finished, but this might only be worth while on very large tables.

Jasminka Vukovic

Microsoft Dynamics NO

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.