Technical Upgrade NAV5.0/Native -> NAV5.1/SQL

zeon

We have a customer that currently runs NAV 5.0 with native db. Now they want to switch to SQL2005.

Wouldn't it be worth the effort to do a technical upgrade to NAV 5.0 SP1 at the same time to take advantage of the new enhancements in 5.0 SP1 regarding to SQL performance (e.g bulk inserts, indexed views etc.)?

The customer runs posting of sales orders with around 300K-500K sales lines, so from my perspective doing a technical upgrade is advisable?!

/zeon

David_Singleton

zeon wrote:

We have a customer that currently runs NAV 5.0 with native db. Now they want to switch to SQL2005.

Wouldn't it be worth the effort to do a technical upgrade to NAV 5.0 SP1 at the same time to take advantage of the new enhancements in 5.0 SP1 regarding to SQL performance (e.g bulk inserts, indexed views etc.)?

The customer runs posting of sales orders with around 300K-500K sales lines, so from my perspective doing a technical upgrade is advisable?!

/zeon

With orders that big you are going to need to do a lot of testing. And when you have finished testing, do some more testing, and once you think you are ready, do some more testing.

The only thing that will tell you if this will wok is by testing.

davmac1

Is this a single order with 300K to 500K lines or the total lines for the day?
In SQL Server, every transaction gets logged, and this is going to be one massive transaction if a sales order has 500K lines in it.

zeon

This is one sales order with 300K-500K lines. The customer have approx. 20-30 orders with that amount of lines. These are posted using the standard batch posting procedure. No code changes have been made to the standard posting process. The orders are posted only once a month during a settlement posting job.

Due to this amount a data I thought it might be worth doing the technical upgrade to take advance of the bulk insert (reducing the amount of time records are locked) and indexed view features, and yes of course we have to test this in depth. But my thought is that it might be an advantage to due the technical upgrade before converting to SQL rather than doing it after the customer is on SQL?

The db is approx 30 gb now, but grows 8 gb a month.

/zeon

Marije_Brummel

With 8GB of database growth a month on a 30GB native database your will enter an interesting time of your life.

Step one is to move to SQL Server before it is to late, a 200 or 400 GB database will be much more difficult to migrate.

Step two is to test step one over and over again to make sure that SQL is not slower to start with

Step three is to analyse what the heck generates 8GB of data each month. The most I had ever (and I did over 300 implementations) was 2,5 GB per month that was caused by massive interfacing and just generating to much data.

David_Singleton

Mark Brummel wrote:

...The most I had ever (and I did over 300 implementations) was 2,5 GB per month.

:shock: Are you sure? 2.5gig is only 30 gig per year?

zeon

Thanks for your input, Mark.

The growth of 8 gb per month is not only caused by posting of the mentioned sales orders, but also from heavy use of Lessor.

The customer is about to upgrade to SQL within the next quarter, so no worries about a 300 gb database.

What are your thoughts about the technical upgrade? As said before I think it'll be an advantage to upgrade to 5.01 in beforehand to take advantage of the new features in this version, in stead of doing in afterwards if we experience performance problems. My thoughts are that anything that could eliminate the problems in 5.0 regarding SQL should be done before upgrading to SQL...

/zeon

David_Singleton

zeon wrote:

...

The db is approx 30 gb now, but grows 8 gb a month.

/zeon

So the system has been live for less than four months? Out of curiosity can I ask why you chose to go with Native?

Marije_Brummel

David, you are correct, it was 2,5 GB per week. Sorry. That makes it about the same as 8 GB per month... :?

Nevertheless, it is just to much to be true...

I would always go for 5 SP 1 or higher. It just does not make sense to go for the old SIFT anymore.

5SP1 "improvements" is just a marketing story by the way. There is no performance improvement, just a new technology.

Buffered inserts does not do much except making it more impossible to debug stuff but I won't go into details... not relevant.

David_Singleton

Mark Brummel wrote:

Buffered inserts does not do much except ....

Not true. Buffered inserts are really useful when selling Navision and the IT person starts to ask if Navision supports standard SQL features. :whistle:

Marije_Brummel

And then when they bought NAV someone tells them that they use a SQL Term for something NAV that works differently.

Buffered insert is definately not the same as Bulk insert.

zeon

David Singleton wrote:

So the system has been live for less than four months? Out of curiosity can I ask why you chose to go with Native?

The system has been live for around 6 months. I wasn't involved in the project in the analysis and design phase, but yes, it's definately a wrong decision to go with native in this case.

/zeon

David_Singleton

zeon wrote:

David Singleton wrote:

So the system has been live for less than four months? Out of curiosity can I ask why you chose to go with Native?

The system has been live for around 6 months. I wasn't involved in the project in the analysis and design phase, but yes, it's definately a wrong decision to go with native in this case.

/zeon

That is a lot of posting to do in one day. As Mark points out, no matter what else you do, you are going to need to look at the volumes and check for ways to cut down the amount of transactions. If this was spread over a month, it would be OK, but posting all that (I calc. about 10 million lines) in one end of month batch is going to be a big job. Isn't there some way of posting daily?

Also you mention Lessor? Is this their payroll module? Do you have a lot of employees?

davmac1

Why does a sales order need 500,000 lines?
What are the lines?
Do you need that level of detail?

I assume their is some type of Business Intelligence software on the back end that analyzes this (and slices and dices), because no human being is going to look at 500,000 lines on a sales order.

kine

Having such a big count of line in one order is not possible in standard (you know, 10000 line numbering, Integer max - it means max. 214748 lines entered by standard process per document). I assume that the order is created by the addon from some automatic process, lines are numbered one by one etc.

Of course, even if you will have e.g. 2ms per line, it means over 16 minutes per document. This is big lock window, during which others are limited in their work. I think that the problem is in the addon design, may be there are some real causes to have this model, but for me it is strange to post such a big documents...

zeon

davmac1 wrote:

Why does a sales order need 500,000 lines?
What are the lines?
Do you need that level of detail?

I assume their is some type of Business Intelligence software on the back end that analyzes this (and slices and dices), because no human being is going to look at 500,000 lines on a sales order.

I can't comment on the customer and discuss this here, but only say, that the customer needs this level of detail. Of course we have been discussing this already due to the fact the posting process takes a lot of time, in which other users are locked. This is actually not a big problem, as they mainly use NAV for this process.

The posting is now done every week instead of once a month to cut down the amount of time it takes to post the orders.

I wasn't clear when I said the process consists of 20-30 orders with 300K-500K lines. Some orders have that many lines, but some does not have more than 20K. The total amount of lines for all orders is around 1,000.000 sales lines at the highest level.

Thanks for your input, guys!

/zeon