How to pre-create column in SQL Server to speed up Nav FOB import

njhansen

We've been using Nav 2009R2 for about five years now, and the process of adding new columns to certain large tables (sales invoice line, etc.) has become agonizingly slow.

We've experimented with adding the column at the SQL Server level in an attempt to have the column "prebuilt" when the FOB import for the table happens, and a process that currently takes a couple of hours (creating huge locking trees if we attempt to do it any time other than wee hours) seems as if it could happen in a few minutes if we could just get the details correct -- as in running a script to add the column, set the default values, and clean up any constraints that differ from what Nav generates takes a few minutes.

But no matter how perfect the table looks, Nav still decides that it needs to add the column instead of using the existing column, which of course fails because the column name must be unique.

Does anyone know enough about the Nav internals to know what it is that Nav uses to determine which one of its columns corresponds to which one of the SQL Server columns? Is there some metadata somewhere that we could discard and rebuild after importing from text instead of FOB?

The fact that this post http://vjeko.com/how-i-reduced-data-upgrade-time-by-78-hours/ involves renaming columns at the SQL Server level seems to imply that what we are doing should be possible, but the details are obviously different.

NavNab

Here is a SQL script you can try. Do it at your own risk (in a test environment of course) + Read the comments carefully.

USE [MyDatabase];

--STEP 1: This will rename your table to a TMP table
--Repeat for all companies
EXEC sp_rename 'MyCompany$Sales Invoice Line', 'MyCompany$Sales Invoice Line$TMP';

--STEP 2: This will create the same structure as your original table
--Repeat for all companies
SELECT TOP 0 * INTO [MyCompany$Sales Invoice Line] FROM [MyCompany$Sales Invoice Line$TMP];

--STEP 3: Open NAV and create your new column in NAV (save and compile your table). Then close NAV.
--For example, create MyNewColumn as CODE 20
--Attention: at this step, your NAV table is empty

--STEP 4: Create your new column in SQL level in the TMP table
--Repeat for all companies
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE [MyCompany$Sales Invoice Line$TMP] ADD
MyNewColumn varchar(20) NOT NULL
--This is an example if you wish to create a column as CODE 20
--This is the tricky part as you should know what SQL type corresponds to NAV type
GO
ALTER TABLE [MyCompany$Sales Invoice Line$TMP] SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

--STEP 5: drop your table
--Repeat for all companies
DROP TABLE [MyCompany$Sales Invoice Line]

--STEP 6: Rename back the TMP table to original name
--Repeat for all companies
EXEC sp_rename 'MyCompany$Sales Invoice Line$TMP', 'MyCompany$Sales Invoice Line';

--STEP 7: open NAV and check your table (data & structure)

NavNab

Here is a SQL script you can try. Do it at your own risk (in a test environment of course) + Read the comments carefully.

USE [MyDatabase];

--STEP 1: This will rename your table to a TMP table
--Repeat for all companies
EXEC sp_rename 'MyCompany$Sales Invoice Line', 'MyCompany$Sales Invoice Line$TMP';

--STEP 2: This will create the same structure as your original table
--Repeat for all companies
SELECT TOP 0 * INTO [MyCompany$Sales Invoice Line] FROM [MyCompany$Sales Invoice Line$TMP];

--STEP 3: Open NAV and create your new column in NAV (save and compile your table). Then close NAV.
--For example, create MyNewColumn as CODE 20
--Attention: at this step, your NAV table is empty

--STEP 4: Create your new column in SQL level in the TMP table
--Repeat for all companies
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE [MyCompany$Sales Invoice Line$TMP] ADD
MyNewColumn varchar(20) NOT NULL
--This is an example if you wish to create a column as CODE 20
--This is the tricky part as you should know what SQL type corresponds to NAV type
GO
ALTER TABLE [MyCompany$Sales Invoice Line$TMP] SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

--STEP 5: drop your table
--Repeat for all companies
DROP TABLE [MyCompany$Sales Invoice Line]

--STEP 6: Rename back the TMP table to original name
--Repeat for all companies
EXEC sp_rename 'MyCompany$Sales Invoice Line$TMP', 'MyCompany$Sales Invoice Line';

--STEP 7: open NAV and check your table (data & structure)

njhansen

Thanks, we tried it in test last week, and over the weekend in live, and it seemed to work.

NavNab

I'm glad it helps.
I forgot to mention that if your new column is included in a key then you should handle this also in SQL (but in this case I do not recommend to do it SQL way )