Navigate Into Success: How I Reduced Data Upgrade Time By 78 Hours

[Blog bot]

Upgrade projects are lots of fun. They are full of challenges that keep you busy day and night all the time.

I have encountered a very interesting challenge on my last upgrade project. The object upgrade process was completed, the data upgrade procedure ready to go, but when we started first tests we realized that the data upgrade execution takes some 39 hours to complete just Step 1. Without even bothering to measure Step 2, we realized we need to do something about it. The customer is running a 24/7 business, and cannot accommodate for such a large downtime, just to upgrade data from NAV 2009 R2 to NAV 2013 R2.

Eventually, we got it down to under a second. If you want to learn how, read on.



At the heart of the problem there was G/L Entry table. There are 11 companies in the database, and the largest one has 54 million records in the G/L Entry table, so it can’t really be fast. But what was the reason that the G/L Entry table was involved in the Step 1 of the upgrade procedure in the first place? Now this was funny.

For this reason or other, in this specific localization, two code fields in the G/L Entry table have been declared as Text[20], instead of Code[20] since a very old version of NAV. They are G/L Account No. and Bal. Account No. and I am still trying to wrap my brain around the fact that somebody thought it was a smart idea to set these two fields to type Text. Microsoft has decided to change these to Code[20] and it was a decision that was not as smart as it was necessary.

Changing a field type is not rocket science: in step 1, move the fields to the upgrade table, empty the fields in the original table, and then in step 2, change the type of the field, move the fields from the upgrade table, and then empty and delete the upgrade table. Easy.

Everybody knows that some things are simply impossible until somebody, who doesn’t know that, makes them possible. (Albert Einsten, probably apocryphal)

However, doing that with hundreds of millions of records spread across 11 companies can by no means be a fast procedure, at least not the way how C/AL goes about it. We tried to beef up hardware, but it helped only a tiny fraction. We were still in high 30-ies in terms of hours for Step 1. Step 2 will take exactly the same time, because it needs to do exactly the same kind of work to move the data back to original fields.

Obviously, NAV with it’s loopie-loopie cursor type of data upgrade process is not the fastest animal on the farm. We needed to take this down to SQL if we really wanted to improve the speed.

Our next step was to give up on the upgrade table altogether. We added two additional fields to the G/L Entry table, both of type code, and we wrote a SQL script to copy the content of the original two fields to the two new fields, and then to set the two original fields to ‘’. And it helped. Step 1 was reduced from 39 hours down to 2 hours, meaning 4 hours down from 78 hours for both data upgrade steps. Quite an improvement. But still, the whole upgrade thing was around 18 hours of data-munching, which puts some pressure if we want to complete it in the course of a night – so we needed to squeeze out some more performance out of it.

An idea popped up to me, and it came from the way how NAV 2015 handles syncing data through the upgrade codeunits. Something like that must be possible. However, it would take quite some work, because there are tons of SIFT views and keys on the G/L Entry table that it makes it extremely difficult to write a script that properly kills and then restores all the object infrastructure.

Finally, I came up with this concept:

1. Add two columns to the table using Development Environment.
2. Drop the two columns (the same ones I added in step 1) from the underlying table using T-SQL. I now have my table out of sync, because the table object contains the two columns that are no longer present in the table.
3. Rename original columns to the names of the two dropped columns using T-SQL. At this step, my table is still out of sync, but now my original columns are renamed –so effectively I have “moved” data to the two new columns I added in step 1.
4. Add the two new columns with the original names, using T-SQL. Assign default constraints to both of them, so that they automatically insert a blank value to the field. At this stage, my table is back in sync, but the data from original columns is now in the new columns.
5. Drop the default constraints from the table. I don’t need them because I have declared the columns as NOT NULL.

All in all, this took under a second to complete for all the 11 G/L Entry tables for each of the 11 companies. In step two, you just need to reverse the process, and it again takes under a second to complete.

Here’s the example of the script.

ALTER TABLE [Company1$G_L Entry] DROP COLUMN [Temp GLAccountNo], [Temp BalAccountNo];exec sp_rename 'dbo.[Company1$G_L Entry].[G_L Account No_]', 'Temp GLAccountNo', 'COLUMN';exec sp_rename 'dbo.[Company1$G_L Entry].[Bal_ Account No_]', 'Temp BalAccountNo', 'COLUMN';ALTER TABLE [Company1$G_L Entry] ADD [G_L Account No_] [varchar](20) NOT NULL CONSTRAINT [Company1$def_gl] DEFAULT '', [Bal_ Account No_] [varchar](20) NOT NULL CONSTRAINT [Company1$def_bal] DEFAULT '';ALTER TABLE [Company1$G_L Entry] DROP CONSTRAINT [Company1$def_gl], [Company1$def_bal]Of course, when migrating data back, the script is slightly different:

ALTER TABLE [Company1$G_L Entry] DROP COLUMN [G_L Account No_], [Bal_ Account No_];exec sp_rename 'dbo.[Company1$G_L Entry].[Temp GLAccountNo]', 'G_L Account No_', 'COLUMN';exec sp_rename 'dbo.[Company1$G_L Entry].[Temp BalAccountNo]', 'Bal_ Account No_', 'COLUMN';ALTER TABLE [Company1$G_L Entry] ADD [Temp GLAccountNo] [varchar](20) NOT NULL CONSTRAINT [Company1$def_gl] DEFAULT '', [Temp BalAccountNo] [varchar](20) NOT NULL CONSTRAINT [Company1$def_bal] DEFAULT '';ALTER TABLE [Company1$G_L Entry] DROP CONSTRAINT [Company1$def_gl], [Company1$def_bal]And thus these two little scripts have reduced the total upgrade time by 78 hours. Of course, the whole upgrade process still takes some 6 hours to execute, but there is a huge difference between 84 and 6.

I hope you find this little trick useful, and I hope it saves you some time in your time-critical upgrade projects.

Read this post at its original location at http://vjeko.com/blog/how-i-reduced-...me-by-78-hours, or visit the original blog at http://vjeko.com. 5e33c5f6cb90c441bd1f23d5b9eeca34</img> </img>

Читать дальше