Performance: Retrieve product from cache in MStorageReservation

Description

We had a scenario where more than 100 lines are added to material receipt. Completing MR takes more than 25s. After debugging code, one of the culprit I found was retrieving product in MStorageReservation which takes around 30ms for each line so for 100 lines its 30x100 = 3000 (3s). I tried retrieving product from cached get method and overhead of 3s reduced.

I wanted to confirm as i would not sure if there could be any impact of this change. Please find attached patch for the same.

Environment

None

Activity

Show:
Carlos Ruiz
May 2, 2018, 8:22 PM

Thanks ,

I was not able to reproduce the issue with the default configuration, so I suspect you can have a serious issue with your product table.
Usually that's caused by adding a slow virtual column in M_Product - in general is better to avoid virtual columns - and must be almost forbidden in high traffic tables like M_Product.

I simulated the case adding a virtual column with the function pg_sleep(1) in M_Product and then it was notorious what you said.

Committed a fix here
https://bitbucket.org/idempiere/idempiere/commits/2b038d6

Regards,

Carlos Ruiz

Pritesh Shah
May 2, 2018, 8:30 PM

Thanks for quick update.

I dont have virtual column in m_product table. However I have millions of product in a table. As you have confirmed this solution, i would go ahead and release it in my code base

Pritesh Shah
May 8, 2018, 7:04 PM

Hello Carlos,

I just saw the change you made in ProductCost.java related to cached get product method, it was there till https://idempiere.atlassian.net/browse/IDEMPIERE-2057 and in this ticket from cached get method it was changed to new object. This just to verify if it could break anything

Carlos Ruiz
May 9, 2018, 10:29 AM

Thanks for peer reviewing.

I made tests for costing and everything was working fine.

Which I found failing is the ticket IDEMPIERE-2057, is not working fine for AveragePO, I'm opening a new ticket with the findings.

Fixed

Assignee

Carlos Ruiz

Reporter

Pritesh Shah

Labels

None

Tested By

None

Priority

Major
Configure