Re: [idempiere] Performance problems

352 views
Skip to first unread message

Carlos Antonio Ruiz Gomez

unread,
Aug 27, 2013, 7:14:00 PM8/27/13
to idem...@googlegroups.com
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

unread,
Aug 28, 2013, 2:27:22 AM8/28/13
to idem...@googlegroups.com
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

unread,
Aug 28, 2013, 2:54:31 AM8/28/13
to idem...@googlegroups.com
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

unread,
Aug 28, 2013, 3:00:26 AM8/28/13
to idem...@googlegroups.com
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

unread,
Aug 28, 2013, 4:01:13 AM8/28/13
to idem...@googlegroups.com
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

unread,
Aug 28, 2013, 10:32:25 AM8/28/13
to idem...@googlegroups.com
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.


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

Regards 

Denis

Philippe Baumann

unread,
Aug 28, 2013, 4:35:54 PM8/28/13
to idem...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages