November 26, 2010 at 11:52 pm
Hi
I have a problem that i couldn't find the reason. We have a erp application running on sql server 2008 and we are running MRP on every wednesday. It takes about 8 hours. It is a client server application and when i trace the server i see lots of select query (about 50-100 per second) the queries are generally control queries after insert and generally returns 0 rows. I tried the application on 5 clients running windows 2003 server. The two clients are three times faster then other three client. And also the fast clients's hardware configuration is lower then the slower machines.
For example
The fast machine is 4 cpu 3gb ram
The slow machine is 16cpu 32gb ram
I tried the change network cables and the result is the same.
After that i realise that the faster machines are running another applications in the same database such as web services. And the slower machines are free and dont have any application on them.
Is there any relation about the performance for example connection pool or any other reason. Because the faster clients runs webservices and they opens and closes many connections.
What do you think?
November 27, 2010 at 2:47 am
I think the major issue is the concept in general, not the performance varying between different systems.
8 hrs for an MRP run??? :crying::pinch: Sounds like a SubmittAndPray solution...
The main reason is most probably a row-by-row-processing (since you see that many selects). If the concept would be changed to use database functionalities (= st-based solution), you'd probably end up with several minutes instead of several hours. A performance gain you won't be able to achieve no matter what hardware your're throwing at a probably poorly designed MRP concept.
Btw: how do you deal with inventory changes during those 8hrs, (especially receiving goods) since you most probably have to refer to the latest packing list number. Are you sure the results you're getting are correct at all? Or do you refer to a previous packing list, but consider the amount just received in your current stock?
How about some intermediate quality control checks during your MRP run where QA would mark 50% of your stock as defect?
Or do you run your MRP during night hoping desperately the stock won't change in between?
Again: From my point of view it's due to a poor concept. Not a poor hardware.
November 27, 2010 at 3:01 am
Thank you for your reply. I know that the main reason is row by row processing but we cant change the erp system substructure. Because it was not written by us.
And our database is about 400gb and we have about 60 million row in stock transaction tables for one year. MRP proccesing also runs in this table. So the time is normal for row by row processing.
We run the mrp at night at it is independent from the inventory changes. Because our formula is,
Amount the produce=(Taken order-Sent order)-Stock-(Planned Production Amount-Act Amount)
And we run the mrp for the next week's orders. So there is no problem about the calculation.
But it is very interesting. In some machines it takes 3 hours for the others it takes 8 hours. The faster one also running another applications on web service.
November 27, 2010 at 3:35 am
Ok, I stop ranting about poorly designed MRP concepts... Just one last thing: An MRP system (among many other things) should not care about the annual number of stock transactions. Nuff said.
If you want to find the reson for performance difference (which is obviously almost only related to hardware issues) you might want to use Performance Monitor to detect where the different results are coming from Here's a link that might help you get started.
If you know you have a badly designed (RBAR) concept that you cannot change, you should at least use a dedicated system doing nothing else but the MRP run. Right now it looks like the current MRP server is the bottleneck since there are too many processes running (either causing low memory or an I/O bottleneck).
Once you have system that is fast enough to fire an extensive amount of queries against your database, you soon may face locking/blocking or even deadlocking issues (depending on how "smart" the MRP software is in terms of parallel processing)...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply