Re: [idempiere] Performance problems

[Carlos Antonio Ruiz Gomez]

Hi Philippe, is the issue mostly related to product, or is it notorious
with other lookups?
Is it on sales order window? or the same in other windows?

Can you please check and compare performance disabling the virtual
column M_Product.CostStandard (and please give us feedback about).

Regards,

Carlos Ruiz


On 27/08/13 08:38, Philippe Baumann wrote:
>
> Hello everybody,
>
> In the following cases we have serious performance problems:
>
> a) Web UI slows down if a user has more than one tabs open, for
> example "SalesOrder"-tabs.
>
> b) Poor performance by lookup rows in a window as described below.
>
>
> Our Environment:
> UBUNTU Server 12.4
> Postgres 9.1
> Java 1.6
> idempiere: Memory usage defintion: Standard. We are using the ZK Web UI.
>
> Our customer has approx. 40'000 products in the database. When
> we call the product window with the "Lookup Record" (not Product Info)
> without any filter criteria the selection results in a timeout.
> Doing the same thing using filter criteria we get the following result:
>
> 1. Filter with a result of 6 rows: 2-3 seconds (fine)
> 2. Filter with a result of 72 rows: 17 seconds (....)
> 3. Filter with a result of 194 rows: 38 seconds (....)
>
>
> A test using the same filter criteria as we used in the test above,
> but with a
> defined restriction of "Max Query Records=20" in AD_Role shows following
> results:
>
> 1. Filter with a result of 6 rows: 2-3 seconds (fine)
> 2. Filter with a result of 72 rows: 10 seconds (....)
> 3. Filter with a result of 194 rows: 21 seconds (....)
>
> Has anybody made same experiences and could solve them?
>
> Kind regards,
> Philippe

[Philippe Baumann]

Hi Carlos,

First of all: THANKS for the FAST and short RESPONSE.

1. Column M_Product.CostStandard
Deactivating this column brought the performance boost:
Selecting 40'000 Products (without any filter criteria) in 3 seconds!!!!!!

2. "... or is it notorious with other lookups? "
Currently we don't know because we don't have so much rows in over tables.

3. "...Is it on sales order window?  or the same in other windows? "
a) This issue has only be experienced with the sales order screen.
b) In this screen we have build-in some virtual columns..... We knew, that this could decrease the performance, but not in a manner like this (see m_product). We have to test now if the performance issue is solved, when we deactivate all this new virtual columns in sales order too.
I will keep you informed.

Kind regards,
Philippe

[Heng Sin Low]

Hi Phillippe,

Can you try whether the following index will help when Column M_Product.CostStandard is enable:

CREATE INDEX M_Cost_Product_IDX ON M_Cost(M_Product_ID, M_CostElement_ID);

Regards,

Low

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Philippe Baumann]

Hi Carlos,

I made some tests with the column M_Product.CostStandard:

The SELECT-Statement behind this column is:

SELECT ROUND(SUM(c.CurrentCostPrice),2)
FROM M_Cost c INNER JOIN M_CostElement e ON (e.M_CostElement_ID=c.M_CostElement_ID)
WHERE e.CostingMethod='S' AND c.M_Product_ID=M_Product.M_Product_ID

Finally: I have created an index on M_Cost.M_Product_ID.

The performance boost was not so amazing as it was by deactivating this column, but a select over all 40'000 brouht a result within 5 seconds.

regards,
Philippe

Am Mittwoch, 28. August 2013 01:14:00 UTC+2 schrieb Carlos Antonio Ruiz Gomez:

[Philippe Baumann]

Hi Low,
I saw your message just now.
It shows the same performance boost with your index statement.
Thanks you too Low.

regards,
Philippe

[Denis González]

Hi Phillipe, I guess you have also checked performance tuning guide for postgresql. If not, perhaps it could help you to get another boost for that specific query.

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

A question: Running this query in a psql or pgadmin console gives you the same reponse time?

Regards 

Denis

[Philippe Baumann]

Hi Denis

Thank you for your support.
In our development team we have experiences for optimizing queries in the oracle world. Actually we don't have that experience concerning postgres. We already knew the link you sent us.
If we use virtual columns, we are doing this carefully.
Coming back to your question:
We did also some tests with pgadmin and the m_product table, but never with the virtual column "CostStandard". After the feedback of Carlos, we made some tests with this subquery. Yes, the results of this test was comparable to the execution time within the GUI.
Creating an index on the related fields was the result of this tests.

regards,
Philippe