Slow import-navdata

Phenno

Hi all!

I have a task to import NAV2015 company to an existing database, which can be done with import-navdata cmdlet. The problem is that this procedure is very slow. It lasts for 10-12 hours to import 10GB navdata. Now, I have to import even larger database so I'm expecting to last it longer, if it can be done at all.

I've checked through sql profiler to see what is done in import-navdata operation and, imho, can say that microsoft did all what it could to make it optimal. It uses insert bulk operations with tablock. It also strips all indexes/visfts before import and recreates them after.

What can be done to speedup this or, is there any new idea how to merge two companies from different dbs?

What bugs me is that the same database (part of migration) was imported with fbk (NAV2009) much faster, in 2 hours. What is so different between these version so fbk import is 6x faster that import-navdata?

Kishorm

You could try the following - but make sure you do this when no-one else is using the database that you are importing into and try this at you own risk (maybe try in a test database first)...

1) Backup existing database (the one you want to import into) - just in case
2) Change Recovery Model to Simple - so that transaction file does not grow
3) Extend the existing database to make sure you have enough room to import the new company - to avoid repeatedly growing the database during the restore
4) Make sure MAXDOP is not set to 1 in SSMS - this will help rebuild the indexes quicker
5) Import the new company data using import-navdata
6) Change MAXDOP back (if you changed it)
7) Change Recovery Model back (it was probably set to Full before)

Phenno

Kishorm,

thanx for the reply but I already did this. Maxdop is already set to half of logical processors (as of NAV2013 this is not mandatory to be set to 1).
Also, I tested it with simple recovery model (and got these results).
Also, I set data file and trans log file to be able to accommodate new data.

Regarding maxdop, I've checked this through profiler, and parallelism will occure only on creating of non-clustered indexes (I think) but that is fast enough already, the slow part are import bulk commands.

Kishorm

Not sure what else to suggest other than is the navdata source file on different physical disk(s) to the SQL database data file that you are importing into - just thinking that this will avoid disk thrashing between reading from navdata and writing to sql data.

Phenno

Kishorm,

already did that, also... Now I'm pretty much looking for solutions other than import-navdata, maybe to restore full db and than to create some custome bcp export/import or similar. It seems that import-navdata cannot be more faster than this.

Phenno

Now it seems I have a new issue, import-navdata fails while indexing larger tables, I suppose due to timeout. Can this parameter be controlled somehow, with sql command timeout?

Kishorm

Have a look at the "SQL Command Timout" option in the CustomSettings.config file...

https://msdn.microsoft.com/en-us/library/dd355055(v=nav.90).aspx#Database

Phenno

I am aware of this setup parameter though I do not know if it uses it or not. I'm using import-navdata direct to sql, not via instance.

Altought it seems that import via instance does use instance itself since in SQL I see username of service, so, probably it uses this parameter too. Will try both options after changing customsettings and let you know.

Phenno

Some more info. It seems that Import-navdata -DatabaseServer (importing directly to sql server) does not use this config file. It failed after 30minutes of indexing (which is default value in config file.

Furthermore, I tried Import-navdata -ServerInstance, but in that case Company table gets locked and no other users can login nor change company, which is not acceptable in my case.

Any idea where Import-navdata with -DatabaseServer switch gets config?

Phenno

Ok, there is a workaround for locking, you can create empty company with the same name, first, then import-navdata via instance. In that case Company table is not locked and users can login and switch companies.

I'm trying import via service and, hopefully, would get config parameter from config file.

Nevertheless, I'm searching for answer where does Import-navdata via DatabaseServer gets configs?

Phenno

It seems that SQLCommandTimeout parameter is used when import-navdata is set with -ServerInstance switch but can't get it work with -DatabaseServer switch.

JasminkaT

Another swtich has been added to this cmdlet in later builds -committpertable. This will help for issues like the one you have. It will, as the name implies, committ the changes after each table, so if it does fail in the end, you will have to clean up (delete) company data manually.

Phenno

Unfortunately, this is NAV2015. That switch is from NAV2016 CU9 I think. Does NAV2015 later cus include this switch also?

As I said, it went ok with -ServerInstance whitch, obviously, consumes sqlcommandtimeout from config.

Phenno

Is there any other option to merge new company into existing database besides import-navdata? (new tenant is not an option, though it's much faster to simply restore database and then connect it as new tenant).

davmac1

have you checked how the RAM is being used for SQL Server and and how the service tier RAM used grows?
Could be you are having memory pressure and Windows is swapping, instead of keeping everything in memory.

Phenno

I will check that too, but, I was trying import with DatabaseServer switch and it was slow too so it is not the instance memory issue (Instance is not used at all in that case). And it starts slow immediately, while RAM is free.

Whatsoever, will chwck it.

vremeni4

Hi,

I think it might be better to use SQL restore and then to copy the data from that other database into the production.
There is a script created by Kine that does this.
I used in in the past successfully
Take a look at this link

[url="http://blogs.msmvps.com/kine/2014/01/22/copy-microsoft-dynamics-nav-company-by-sql-script-into-another-database-v2/[/url]

So the steps would be.
1. restore the database
2. change the script parameters above to copy the data from the resorted database into production database.

I hope this helps.

Phenno

Vremeni, thanx for the link, it's worth testing it.

Since you used it, did you marked times needed to finish job?

vremeni4

It is much faster then NAVdata import, that is all I remember.
It literality copies the data from one SQL table to another.

vremeni4

sorry i think the link above is not corect
blogs.msmvps.com/kine/2014/01/22/copy-microsoft-dynamics-nav-company-by-sql-script-into-another-database-v2/

Phenno

Ok, I will test it and let you know.
Thank you for information.

Phenno

Vremeni,

I tried it in test environment and got almost 4x faster time than with import-navdata. Now I'm getting acceptable 13hours of import of 250GB.

One must check if table structure is exactly the same first (so as with import-navdata with difference that import-navdata will break full transaction in case of difference, while Kine's script will only skip that table).