Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Technical Upgrade NAV5.0/Native -> NAV5.1/SQL
zeon
Member Posts: 130
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
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
0
Comments
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.
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.
http://mibuso.com/blogs/davidmachanick/
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
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.
:shock: Are you sure? 2.5gig is only 30 gig per year?
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
So the system has been live for less than four months? Out of curiosity can I ask why you chose to go with Native?
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.
Not true. Buffered inserts are really useful when selling Navision and the IT person starts to ask if Navision supports standard SQL features. :whistle:
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.
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?
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.
http://mibuso.com/blogs/davidmachanick/
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...
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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