I had no clue how good my laptop was. Seriously. Today it kicked ass of an 8-processor server.
Tomorrow we have a go-live of a Microsoft Dynamics NAV deployment, with manufacturing customized to support configure-to-order functionality. Refreshing manufacturing orders now calculates dynamic BOMs and routings, and it takes time.
This is my laptop:
- HP Compaq 6910p
- Intel Core2 Duo, 2.40 GHz
- 4 GB RAM
- Windows Vista Enterprise, 64-bit
- SQL Server 2005 Developer, 64-bit
- Lousy single 5400 RPM SATA drive (heavily fragmented, with SQL data and log files obviously residing on the same volume)
This is the server:
- HP ProLiant (don’t know the exact model)
- Intel Xeon MP 3,16 GHz (eight of these in total)
- 16 GB RAM
- Windows Server 2003 Enterprise R2, 64-bit
- SQL Server 2005 Enterprise, 64-bit
- Three SCSI disks in RAID 0+1 (with SQL data and log files residing on separate logical volumes)
While testing the manufacturing order refreshing, we noticed somewhat slow performance on server – it takes about 15 seconds to refresh one. I took the database backup, restored it locally on my laptop, ran the refresh of the same manufacturing order, and it completed in 6 seconds. Repeatedly.
I was sure something was wrong, so I established another test database on the server, restored the backup in order to get the same environment (the same freshly restored database), and still, it took 15 seconds to refresh a manufacturing order. I killed all connections, made sure nobody but me was accessing the database, and still no improvement. It was not a locking issue. And it shouldn’t have anything to do with SIFT tables, indexes or anything – the same backup was restored on my laptop and on the server, and the 8-processor server still eats my laptop’s dust.
I didn’t have much time to investigate into this further, but I see where most of my effort will go tomorrow. I have no clue what’s going on, but there is no logical explanation how my laptop, any laptop, could possilby outperform this server.
Any suggestions, anyone?
make sure you update stats and reindex the indexes.
ara3n: Thanks for suggestion. Will definitely try that, but I figured that two databases freshly restored would be equally (un)optimized and (in)effective. I hope I prove myself wrong today…
Did you take a look at other software residing on the server/accessing the server?
I had an unfortunate case where machine with SQL Server was also used as Exchange Server, File Server and Primary Domain Controller. Took me a bit of time to persuade users that was not optimal situation for Navision 🙂
Jurica: Yes, I did have a chance 🙂 There is none. This server is a dedicated SQL machine. Other than SQL Server itself, it is only running a finsql.exe (mine) because my first though about speed issues was that the network was causing delays – so I ran finsql.exe off the server itself, and proven that the network wasn’t a bottleneck in this case.
ara3n: Unfortunately, I didn’t prove myself wrong. Sometimes I really hate when I am right 🙁 So, I rebuilt the indexes, updated statistics, and it did speed up the server, but it did speed up my laptop too. So, now my laptop finishes the task in just under 6 seconds (5.832 on average to be precise), and on server it takes on average 13.39 seconds. How cool is that? So, I was right to assume that a freshly restored database would be equally (un)optimized on both my laptop and the server. So – I’m still pretty clueless 🙁
I’ve noticed as well that Coee 2 Duo based notebooks with a lot of lvl2 cache are darn fast doing certain things, but still a server like the one you specified should outperform it I’d imagine.
How big a database are we talking about here? How big are the tables in question?
Do you have the same client parameters (DBMS Cache) on both notebook and server clients?
Did you try a run with client monitor enabled to see if there is any noticeable slowdown?
Try running Nav client on notebook with DB on the server and vice-versa – any change in performance?
You’re running NAV 4.0 SP3 with hotfixes (or higher version) on your laptop.
What’s the client on the server? Same version? Same hotfixes?
Jurica: No, I am running NAV 5.0 SP1 on both laptop and server.
Can you create a client on the server itself and run from there. That should eliminate, or show, the network as the problem.
Davor:
The database is currently 4 GB in size, the table which gets about 90% of traffic with this procedure is around 300 MB. DBMS Cache is the same.
Unfortunately, I can’t run client on laptop and DB on server, because it’s not in the same LAN. Server is physically 300 km away from where I am, link is pretty slow, so users connect to a terminal server which is in the same LAN. But there is no significant difference between running the client on the DB server itself or on the terminal server – performance is roughly equal, maybe less than half a second faster on the DB, which I can also easily attribute to measuring error, and the fact that terminal server is loaded with users, and DB is not.
Brent:
Yes, we did exactly that yesterday. Installed a client on the server directly, then disconnected everyone and had about an hour of server for our testing. It was almost as fast as when there were tens of users. Really, performance monitor doesn’t show any processor, disk or memory issues, processors are mostly below 15% of load, memory is half-free, and disks are lightning fast. A total mystery, I’ve never seen anything like this.
Talking about configure-to-order, i m trying to research in the web about CTO and ATO. Probably I am new to ERP.
I am talking to a customer whereby their business is very unique – they are project based, oil & gas industries, their products is normally a unique set of item comprise of valves, pumps, pipes, pressure reliefs and etc. The configuration is done by their principal and rarely repeated. So there is no point for them to store the product as stock. Are these guys consider CTO / ATO?
Kenny: I don’t think this should be done with customizing manufacturing for CTO/ATO – I believe you are better off with Jobs module of NAV. Are these realy “items” – is there any maintenance of these? I once worked on a project in Singapore, in oil/gas industry, we had a similar requirement, and it was solved with a local vertical solution for plant maintenance by IBIZ c.s., you can read more about their solution at http://www.ibizcs.com/biz_nav_cmms.htm
Vjeko
these “item” is not really item as they do not want to track it. after the project is over, it will move to warranty/maintenance phase. Checked out the PMS by IBIz but the requirement ain’t cmms. I am stuck here. Appreciate your help.
Kenny,
How does the process go for their sales? From what you say, I figure it is something like this (for the sake of clarity “company” is your customer and “customer” is theirs:
1. The customer orders a product (still sounds much like an installation to me – is it an installation, or is it really a product?)
2. The company asks their principal for configuration (blueprint)
3. The company assembles the product (where is it assembled, at the company’s place, or at the customer’s place?)
4. For this one I’m not sure: The company delivers the product to the customer
How does the invoice look like? Does it contain the list of assembly parts, or the finished product? Does the invoice list assembly services (machine costs, workers costs) as separate lines? Is any scrap involved? How is it handled?
The answer how to do it in NAV depends a great deal on the answers to these questions.
your flow is quite correct.
1. installations + finish product
2. true
3. sometimes the company assemble the product, sometimes the principal send a complete finish product. Principal may direct deliver the finish product to their customer.
4. true
the invoice only have finish project (finish product + services) and not itemised. no scrap involved.
Kenny: if they don’t want to track these items, they can post all the sales directly to G/L accounts – that way the accounting part will be allright, and items won’t be tracked by value. Another possibility is to track items, but with zero-value cost (I wrote about it at http://dynamicsblog.wordpress.com/2008/04/06/purchasing-services-inventory-value-zero/).
For direct delivery of finished products from principal to the customer, you can also use the “Drop shipment” option, it will make it possible to post shipments directly from vendor to customer. With combination of drop-shipments and inventory-value zero, you effectively don’t keep track of the inventory.