SQL script to create dimension entry NAV2009 to NAV2016

rsaritzky

Hi all,

We're testing our planned NAV2009->NAV2016 upgrade, and we have a large database (180GB approx). The first data upgrade test was done on a database that was shrunk down by date-compressing several of the large ledger files. The data upgrade ran in about 50 hours, which doesn't fit into our planned time-window of a weekend conversion (this does not include the other data upgrade steps which take additional time.)

In addition, we have several custom ledger tables that I have not yet converted to dimension set entries, so I know our next full test will take even longer.

We are investigating more server resources, but our test server is brand-new, enterprise-class, lots of RAM, high-speed SAS drives and is only being taxed to about 20% usage during the data upgrade.

I have seen a few references about some people using SQL scripts to create the dimension set entries instead of the Data Upgrade codeunit. But I haven't seen any sample scripts. Has anyone seen any actual SQL script code that I could reference? I'm just trying to avoid having to reverse-engineer everything.

Thanks

Ron

davmac1

the data upgrade codeunit uses SQL scripts for the dimension upgrades. you could start by looking at them and see how you can optimize them.
There is a hierarchy of tables associated with the dimension sets which stops it being as easy as it could be - define all your unique dimension sets, then run a mass sql update

rsaritzky

Thanks for the idea!

Ron

davmac1

the data upgrade codeunit uses SQL scripts for the dimension upgrades. you could start by looking at them and see how you can optimize them.
There is a hierarchy of tables associated with the dimension sets which stops it being as easy as it could be - define all your unique dimension sets, then run a mass sql update

rsaritzky

Thanks David,

I did start looking through the existing data upgrade codeunit - I know now that it constructs SQL code to create temporary tables that store dimension information in a variety of ways - I haven't gotten all the way through the script with an "example" table to see what the SQL statements actually do.

But so far (just for other people to know and perhaps contribute):

1. It creates 3 temporary tables - UpgradeDimTable, UpgradeDimSetID and UpgradeTableID, each named uniquely by company and table, e.g. 'CompanyName$upgrade355_Dim' for the first one.

It then populates those tables by doing various SELECT INTO's - I haven't parsed them all out yet, so I can't post the results (yet).

However, it is currently apparent that due to the complexity of the code, not very many people have chosen to try to reverse-engineer this process to see if it can be tuned.

BUT, in my continuing experimentation, I've discovered that the part of the process that adds the Domain Name to the User ID takes FAR more time than the Dimension update piece. My user-rename process errored out after 56 hours, but the Dimension update process for that same database (with 27,000,00 Ledger Entry Dimension records) took only 3-4 hours to complete.

An experienced NAV developer, Lars Wedman, has posted an article about a basic script/technique to move the User Rename logic into a SQL script, which he claims is 10 to 100 times faster than the NAV code. I'm going to test his code in the next few days. It could be a little-more refined (which I may do in hopes of contributing it to the community), but his reasoning and logic is very sound. If you do updates like this infrequently, it would only take an hour to use his "manual method" to construct the SQL script. If you want to read the article yourself, here's the link:

larswestman.se/speeding-up-step-2-in-nav-upgrades/

Rikt-It

We did it with a SQL-script..


On Your own risk...
--**************************************************************************************************************
-- Christer Berntsson, Softronic, Stockholm, Sweden ,2015-06-17
--
-- Idea from Lars Westman, Umeå, Sweden
--
-- Update columns in NAV-2013--> with domainname
--
-- This script should be run in SQL-server Management Studio
--
-- 2015-06-17: In a Nav2015-database with 8 Companies and Size of 87GB on SSD-discs it took aprox 5 minutes.
--
--**************************************************************************************************************
USE [Nav2015_XYZ] -- <<-- Your database
GO

--Use a temporay table
CREATE TABLE [#User ID Fields] (Tablename varchar(250), Fieldname varchar(50), Size int)

--Fill temporay table with tablename and field
INSERT INTO [#User ID Fields] (Tablename, Fieldname, Size)
SELECT
o.name
,c.name
,c.max_length
FROM
sys.columns c
Join sys.objects o on o.object_id = c.object_id
WHERE
c.name like '%User_ID%'
and o.type_desc like 'USER_TABLE' --only usertables
and c.system_type_id = 231 --only textfields
-- add filters and exceptions
ORDER BY
o.name

--DEV:
--select * from [#User ID Fields] -- Show table who contain '%User_ID%' and is text-fields

DECLARE @sql nvarchar(MAX),
@CompanyName nvarchar(254),
@Tablename nvarchar(254),
@Fieldname nvarchar(254),
@Domain nvarchar(254),
@Size int

SET @Domain ='TheDomainName'

--Start loop all Companies and Tables and Fields
DECLARE UpdateCursor CURSOR FORWARD_ONLY FOR
SELECT Tablename, Fieldname, Size FROM [#User ID Fields]

OPEN UpdateCursor
FETCH NEXT FROM UpdateCursor INTO @Tablename, @Fieldname, @Size

@FETCH_STATUS = 0
BEGIN
PRINT getdate()
SET @sql = 'UPDATE SET = CASE WHEN LEN()<>0 THEN '''+ @Domain+'\''+REVERSE(LEFT(REVERSE(),CHARINDEX(''\'',REVERSE(''\''+))-1)) ELSE END'
PRINT @sql
EXEC (@sql)
FETCH NEXT
FROM UpdateCursor
INTO @Tablename, @Fieldname, @Size
END

--Clean up
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
DROP TABLE [#User ID Fields]

rsaritzky

Thanks for the idea!

Ron