Performance of NAV/SQL Database

njaiku

Hi,

We have developed Addon module in NAV 2009. To check the performance of database
a)A separte machine installed with O/S 2003,SQL Server 2005 standard edition and NAVISION 2009 which acts as a server.
b) Through the client we get connected to the machine.
c) I have inserted 50,000 records in the database and i posted these records into G/L table as batch. It takes 1 hr 30 min.
d) For spooling the reports it take 45 min
e) we check for concurrent users by posting the records. For the first 3 users posting happens one by one and for the fourth user deadlock situation occurs

where should i have to tune the performance of this database whether in SQL 2005 or in coding of database?

Looking forward for your valuable suggestions.

ilayarajav

Performance of the database not only depend on the SQL Server, It depends on the lot of other factors

1. SQL Maintenance needs to be carried out periodically, such as reindexing, stats, etc. There are lot of discussion avialble in the forum
2. If it in Customized code, need to check no complicated coding involved.
3. Table Locking is the general RDMS behaviour. Need to check the coding to avoide deadlock situation
4. Hardware configuration is also one of the factor decide the performance
5. If reports consists lot of calculation and multiple table reference then ultimately report generation speed will get affected.
6. Ensure latest service pack installed on the system.

Regards,
Raja

vicky_dada

hi, i have a similar situation I have done rebuld indexing on all the tables updated statitis, still my posting is getting slow specially with transfer orders. however after rebulding index the time is bit increase before it was 3 mins now its taking 1.5 mins.

any other check did i miss?

garak

did you read the "long" thread in the SQL Performance subforum about these theme and the tread with the indicators for the SQL Profiler to find out why the system is so slow? There are some posts (a lot of) about these.

So read this before, there you can find how to use SQL Profiler, perfmon, client monitor, some SQl scipts, what for maintenance plans u nedd, how your database must configure, which storage u need, which RAID you not should use and so on .....

regards

generic

I suggest that you give up on the addon and implement it for a different ERP.
Std NAV cannot be used in high performance ERP.

Also I don't think you are getting deadlocked, but just blocked.

If you are deadlocked, then you are not locking the table in right sequence.

Slawek_Guzek

generic wrote:

Std NAV cannot be used in high performance ERP.

Funny statement...

How do you call 12500 order lines posted per day? I would rather call it high performance. And I'm sure that there are quite few fellows on this forum posting bigger numbers...

Slawek.

generic

Wow 8.6 Order lines per minutes. If you have 5-8 lines in order, then you have about 1 order per minute.
I'm assuming 24 hours per day.

If it's 8 hours then it's 26 Order lines per minute. If you have 5-8 lines in order you have about 3 to 5 orders per minute.


And I'm sure you modified the db to make it to work, I'm wondering how many hours of performance has been done.
You've probably unchecked post cost to gl and running this at night, or expected cost unchecked. How long does your Inventory valuation report take? And I won't talk about how many locking error people get during the day.

Slawek_Guzek

generic wrote:

Wow 8.6 Order lines per minutes. If you have 5-8 lines in order, then you have about 1 order per minute.
I'm assuming 24 hours per day.

12500 is peak in 'hot' time. The average is 1.2 order line per order roughly.. Main stream of orders comes within 12 hours, when telesales staff is working, but www can take order 24hr/day of course.

Indeed all unnecessary stuff is turned off, and orders are posted overnight in batches to eliminate locking - sales people (and web interface) only release the orders. And all is running on VERY decent hardware.

Regards,
Slawek

DaveT

generic wrote:

Wow 8.6 Order lines per minutes. If you have 5-8 lines in order, then you have about 1 order per minute.

For example EDI orders for a Retail chain - a lot of order lines in less that a minute

generic

Look at this thread.

viewtopic.php?f=16&t=35983


It has 1000 users posting on 290 GB db. 400 sales people and they posted 50K orders per hour.

As I said again, NAV performance is a joke.

garak

Thats true, but NAV is not designed for that. you can optimize it, but you're limitated. so if you need a product that post over 50k transactions in a hour, than you should make a perftest to see if you application can handle this or if you must use other ERP applications (if they can handle this).
Every ERP has his good points and bad points. I'm sure that in the future, step by step, MS will improve some "bottle necks". but this need time. so, generic, if you doesn't have this time and you looking for an ERP that can handle this transactions, try to optimize your source and your storadge etc, or take a look to a other application.

regards

Mally

Dear concern
How can we rebuilt indexes in SQL?What r steps to do this task?

garak

go to the SSMS (SQL Server Management Studio) and create a new query. Type there "ALTER Index" mark it and press F1 to read the books online.

http://msdn.microsoft.com/en-us/library/ms180074.aspx

Also you can create separate jobs for specific tables. Search also the forum for this

Slawek_Guzek

generic wrote:

As I said again, NAV performance is a joke.

OK, so what number of postings per minute is NOT a joke ?

generic

I've provided a link for a comparison for GP. Before MS bought Navision, Nav was competing with GP.
5 years later, Nav is stuck in that same 50-100 users, while GP can run 1K users and can run 10 million transaction in one day on a 290 gig db.

They were both in small to mid market ERP system. And GP providing these stats is not a joke.

While GP has innovated to a point where they can run 10 Million transaction with 1K users, Nav is stuck on a chocking point.

The NAV databases are growing to 50 to 100 gig and performance has not been improved.
https://mbs.microsoft.com/partnersource ... litywp.htm

Slawek_Guzek

generic wrote:

...while GP can run 1K users and can run 10 million transaction in one day on a 290 gig db.

They were both in small to mid market ERP system.

Right.

10 mil/1k users 'working' = 10 000 transactions/user/day = 416 transactions/user/hr (assuming 24 hr work/day) = 7 transactions /user/minute.

Do you really think it is possible ? Do you think it is really necessary ? What kind of 'transacions' they are talking about ? Document level transactions or maybe database level transactions ?

Don't you think this statistic is just a marketing bullsxxt, and you've got excited without any serious thought ?

generic

RTFM before posting and asking questions.

Do you really think it is possible ? Do you think it is really necessary ?

You haven't been in big implementation to ask these stupid questions.
Try and post a sales order with serialized item with qty of 100 and then see how many transactions it creates.

Again RTFM, and tell me something that is bullSh*@t in it.

Alex_Chow

generic, that whitepaper released by Microsoft is marketing BS. I'm not sure why you insist on taking it word for word. Like I've said before, if Microsoft really wanted to, they can write a similar case study based on the 1200 user NAV implementation that p.willism9 did.

Not nice to be calling people stupid. [-X

Have some guts and not hide behind your alias when you seem to be hell bent on destroying Navision and this community's reputation. If you really know what you're talking about, tell us your real name. So at least when we meet, I can buy you a beer.

generic

Alex Chow wrote:

Not nice to be calling people stupid. [-X

I didn't call him stupid. I said the question were stupid, because they are in the document that I linked in.

I'm not destroying anybody's reputation. MS does release Nav documents and they are in 50 user. In one of my threads I listed has the link.

And as for my name, It's Yogesh.

Alex_Chow

generic wrote:

I didn't call him stupid. I said the question were stupid, because they are in the document that I linked in.

From your post, it sure didn't come off that way. By saying the question was stupid, you're basically implying the person asking the question is stupid. Unless I'm reading too much into this...

generic wrote:

I'm not destroying anybody's reputation. MS does release Nav documents and they are in 50 user. In one of my threads I listed has the link.

You're discrediting the NAV product by insisting on marketing materials that's released by Microsoft. And from your other threads, instead of recommending a proper solution, you're first solution is to suggest people to look for other ERP products.

generic wrote:

And as for my name, It's Yogesh.

Yogesh what? Can I can at least Google you and find some background on you? This way, we know you have the proper background and knowledge to make a point to discredit the product.

generic

Yogesh Lahoti

You can try and discredit anything I've said in these threads.

generic

Alex Chow wrote:

You're discrediting the NAV product by insisting on marketing materials that's released by Microsoft. And from your other threads, instead of recommending a proper solution, you're first solution is to suggest people to look for other ERP products.

NAV is a MS product. All I'm doing is providing another ERP system that is sold in the same market.
I'm not talking about SAP B1 or Sage. You are welcome to provide me marketing documents for NAV.

Your basic argument is that MS is presenting false information and 1K users with 10 Million transaction is false.

Well show me a NAV document that comes event close to that. The reason why you won't find it is because NAV is being pushed into small 50-100 user market and I don't want to repeat what I've already said.

The way the Posting routine work and lock tables you can't do much of performance improvement. And most of the improvement are workaround.

Do you want me to tell him to go an rewrite the posting routine, and while you are it, you might as well change all the ledger table structures, so that you can have concurrent transaction in the db without experiencing locking.

Slawek_Guzek

generic wrote:

You haven't been in big implementation to ask these stupid questions.

It was big enough for me.

One wise man said - There are no stupid questions, only stupid answers. Another said - one who ask may seem to be stupid but it has a chance to learn and get wiser. Really stupid is who doesn't ask as he remains stupid. Forever.

BTW. You haven't answer any of my question in fact. Why ? If they are so stupid they should be easy to answer.

I could also say RTFM. But read it with understanding (hint: single ledger entry <> transaction). Maybe you will find out that numbers there don't really prove what you're saying. Read and focus on "Real-Life Results" section. I will quote it for you:

A leader in the computer and technology industry meets their customer demands by successfully
transacting over 4,000 sales orders a day in Microsoft Dynamics GP.

My real life example was around 10k sales order a day.

Your basic argument is that MS is presenting false information and 1K users with 10 Million transaction is false.

My basic argument is that you got stuck to general 10 000 000 number, and didn't even notice that you're comparing pears to apples.

The real number to compare is 51,049 Sales Orders posted + perhaps 14k G/L Entries. Which is damn far 10 Million, far from your suggestion that GP is capable of posting 10000 times faster than NAV. Note - all the rest are postings to different tables, and 10 million is overall sum of all created entries in all tables.

Do you want me to tell him to go an rewrite the posting routine, and while you are it, you might as well change all the ledger table structures, so that you can have concurrent transaction in the db without experiencing locking.

Very good point. I can do this easily in NAV - can you do this in GP at all ?

ErictP

It is possible to run Dynamics NAV with more then 100 users.

Microsoft case studies
http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000003578

The solution has since become available to more than 650 simultaneous users and consistently maintains extremely high performance.

generic

Well It states that the CRH Europe Distribution has 9 companies (subsidiaries) So 650 / 9 = 72 users per company.
Hardly impressive.

generic

Slawek Guzek wrote:

BTW. You haven't answer any of my question in fact. Why ? If they are so stupid they should be easy to answer.

Do you really think it is possible ? Do you think it is really necessary ?

Just post one post a sales order with serialized item with qty of 100 and see how many transaction it creates.

A leader in the computer and technology industry meets their customer demands by successfully
transacting over 4,000 sales orders a day in Microsoft Dynamics GP.
My real life example was around 10k sales order a day.

So? It also meets the requirement of 5 sales orders as well? What's your point?

In both the real world and computer lab tests,
Microsoft Dynamics GP has proven its ability not only to handle large sales order transaction volumes, but
also to handle the load comfortably when those volumes grow in a successful business.

My point is that NAV can't grow the the volumes grow. or the db grows.

My basic argument is that you got stuck to general 10 000 000 number, and didn't even notice that you're comparing pears to apples.

I wrote 10 mil transaction because it was an easy sum number. I could write 51,049 per hour.

The real number to compare is 51,049 Sales Orders posted + perhaps 14k G/L Entries. Which is damn far 10 Million, far from your suggestion that GP is capable of posting 10000 times faster than NAV. Note - all the rest are postings to different tables, and 10 million is overall sum of all created entries in all tables.

If you look at PDF document, it's 51,049 PER HOUR. Multiply that by 24. Thats 1.2 million Sales Orders per day!!!!!

Do you want me to tell him to go an rewrite the posting routine, and while you are it, you might as well change all the ledger table structures, so that you can have concurrent transaction in the db without experiencing locking.
Very good point. I can do this easily in NAV - can you do this in GP at all ?

They don't have to because it's part of base product. I'll take a product that does it from based product over any custom development that would put the project at risk and may not be successful. And based on question I see here I don't think he will be successful.

If you look at your performance improvement companies for NAV that are available, all they do is make sure sql is running robustly.
WOW that's all they have come up with to boost concurrency and scalability.

ErictP

These 650 simultaneous users only are from the BauBedarf and Richner, the Swiss subsidiaries of CRH Europe Distribution.

generic

ErictP wrote:

These 650 simultaneous users only are from the BauBedarf and Richner, the Swiss subsidiaries of CRH Europe Distribution.

MS provides for GP a document with hardware spec, number of users and what each person is doing. How many sales order, purchase order, cash receipt, GL entries are posted. How big the db is. What the hardware spec is.

You could use the document and reproduce the environment.

I get criticized for this as marketing fluff, but I don't see anybody in here criticizing ErictP for using this as proof.
Mainly because you are so attached to the product and your livelihood depend on it.

As I stated before the document is useless, because those 650 users could be smoking pot and not doing anything in the system.
Or the transaction volume 1 sales order per day.

This is sad that there is this double standard going on in this thread.

David_Singleton

generic wrote:

Yogesh Lahoti

You can try and discredit anything I've said in these threads.

This I find very hard to believe. Unless there is another Yogesh Lahoti, I must say I am totally shocked. The Yogesh I know is an extremely intelligent and informed person. I find it impossible to believe that the garbage written here was written by him.

generic

David Singleton wrote:

generic wrote:

Yogesh Lahoti

You can try and discredit anything I've said in these threads.

This I find very hard to believe. Unless there is another Yogesh Lahoti, I must say I am totally shocked. The Yogesh I know is an extremely intelligent and informed person. I find it impossible to believe that the garbage written here was written by him.

Garbage....
.

you haven't provided anything on the contrary.

David_Singleton

generic wrote:

Garbage.... David you've been the egocentric bully in person as you are
online.

So are you are saying that you are the Yogesh that I know. In that case please reply to the email I just sent you.