Openbravo Issue Tracking System - Retail Modules |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0037633 | Retail Modules | Web POS | public | 2018-01-14 09:51 | 2018-02-22 18:19 |
|
| Reporter | umartirena | |
| Assigned To | rqueralta | |
| Priority | normal | Severity | major | Reproducibility | always |
| Status | closed | Resolution | no change required | |
| Platform | | OS | 5 | OS Version | |
| Product Version | | |
| Target Version | | Fixed in Version | | |
| Merge Request Status | |
| Review Assigned To | marvintm |
| OBNetwork customer | OBPS |
| Support ticket | |
| Regression level | |
| Regression date | |
| Regression introduced in release | |
| Regression introduced by commit | |
| Triggers an Emergency Pack | No |
|
| Summary | 0037633: Search a Business Partner by 'Phone' in 'Remote for Customer' mode raises a JS error with a big amount of customers |
| Description | Search a Business Partner by 'Phone' in 'Remote for Customer' mode raises a JS error with a big amount of customers.
We are trying with 1702935 Customers.
|
| Steps To Reproduce | The following query in psql takes around 6 minutes with that amount of Customers. (Replace the proper ID's)
select businesspa0_.C_BPartner_ID as col_0_0_, businesspa0_.Customer_Blocking as col_1_0_, businesspa0_.SO_Order_Blocking as col_2_0_, businesspa0_.Name as col_3_0_, businesspa0_.TaxID as col_4_0_, businesspa2_.Name as col_5_0_, aduserlist1_.Email as col_6_0_, aduserlist1_.Phone as col_7_0_, businesspa0_.C_BPartner_ID as col_8_0_, '' as col_9_0_, '' as col_10_0_, '' as col_11_0_, '' as col_12_0_, '' as col_13_0_, '' as col_14_0_ from C_BPartner businesspa0_ left outer join AD_User aduserlist1_ on businesspa0_.C_BPartner_ID=aduserlist1_.C_BPartner_ID, C_BP_Group businesspa2_ where businesspa0_.C_BP_Group_ID=businesspa2_.C_BP_Group_ID and (upper(aduserlist1_.Phone) like upper('609%')) and businesspa0_.IsCustomer='Y' and (businesspa0_.M_PriceList_ID is not null) and (businesspa0_.AD_Client_ID in ('7CA9E072466D4B5F835216EFE75892FA' , '0')) and (businesspa0_.AD_Org_ID in ('0' , '95A8BA076D91431490E60AEFE687F544' , '478D26D062E44903830501196588BB21' , '0308CDA931C6482396877288662D3092' , '502853F6F6794AA88710EC1E976ED54B')) and businesspa0_.IsActive='Y' order by businesspa0_.Name asc limit 301
|
| Proposed Solution | Adding the following index in ad_user table increases the speed of the query to 18 seconds, but still not enough:
CREATE INDEX ad_user_phone_idx
ON ad_user
USING btree
(phone COLLATE pg_catalog."default"); |
| Additional Information | |
| Tags | Performance |
| Relationships | |
| Attached Files |  Selection_124.png (360,408) 2018-01-14 09:51
https://issues.openbravo.com/file_download.php?file_id=11457&type=bug |
|
| Issue History |
| Date Modified | Username | Field | Change |
| 2018-01-14 09:51 | umartirena | New Issue | |
| 2018-01-14 09:51 | umartirena | Assigned To | => Retail |
| 2018-01-14 09:51 | umartirena | File Added: Selection_124.png | |
| 2018-01-14 09:51 | umartirena | OBNetwork customer | => No |
| 2018-01-14 09:51 | umartirena | Triggers an Emergency Pack | => No |
| 2018-01-14 09:52 | umartirena | OBNetwork customer | No => Yes |
| 2018-01-14 09:52 | umartirena | Resolution time | => 1516316400 |
| 2018-01-14 09:58 | umartirena | Proposed Solution updated | |
| 2018-01-16 08:35 | marvintm | Resolution time | 1516316400 => 1517180400 |
| 2018-01-19 14:05 | Sandrahuguet | Tag Attached: Performance | |
| 2018-01-25 13:21 | marvintm | Resolution time | 1517180400 => 1518303600 |
| 2018-01-25 16:53 | rqueralta | Assigned To | Retail => rqueralta |
| 2018-01-25 16:54 | rqueralta | Status | new => scheduled |
| 2018-02-15 19:07 | hgbot | Checkin | |
| 2018-02-15 19:07 | hgbot | Note Added: 0102453 | |
| 2018-02-15 19:07 | hgbot | Status | scheduled => resolved |
| 2018-02-15 19:07 | hgbot | Resolution | open => fixed |
| 2018-02-15 19:07 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/8d0fbb9abd2e45d9ad682e6efdf12fdf1da38239 [^] |
| 2018-02-15 19:11 | marvintm | Review Assigned To | => marvintm |
| 2018-02-15 19:11 | marvintm | Status | resolved => closed |
| 2018-02-15 19:11 | marvintm | Fixed in Version | => RR18Q2 |
| 2018-02-16 12:22 | shuehner | Note Added: 0102459 | |
| 2018-02-16 12:22 | shuehner | Status | closed => new |
| 2018-02-16 12:22 | shuehner | Resolution | fixed => open |
| 2018-02-16 12:22 | shuehner | Fixed in Version | RR18Q2 => |
| 2018-02-16 14:15 | hgbot | Checkin | |
| 2018-02-16 14:15 | hgbot | Note Added: 0102464 | |
| 2018-02-16 14:21 | marvintm | Note Added: 0102465 | |
| 2018-02-16 14:21 | marvintm | Status | new => closed |
| 2018-02-16 14:21 | marvintm | Resolution | open => no change required |
| 2018-02-22 18:19 | hudsonbot | Checkin | |
| 2018-02-22 18:19 | hudsonbot | Note Added: 0102771 | |
| 2018-02-22 18:19 | hudsonbot | Checkin | |
| 2018-02-22 18:19 | hudsonbot | Note Added: 0102774 | |
|
Notes |
|
|
(0102453)
|
|
hgbot
|
|
2018-02-15 19:07
|
|
Repository: erp/devel/pi
Changeset: 8d0fbb9abd2e45d9ad682e6efdf12fdf1da38239
Author: Rafael Queralta Pozo <rqueralta <at> nauta.cu>
Date: Thu Jan 25 12:46:30 2018 -0500
URL: http://code.openbravo.com/erp/devel/pi/rev/8d0fbb9abd2e45d9ad682e6efdf12fdf1da38239 [^]
Fixed issue 37633: Search a Business Partner by 'Phone' in 'Remote for Customer'
mode raises a JS error with a big amount of customers
- Added index into ad_user table
---
M src-db/database/model/tables/AD_USER.xml
---
|
|
|
|
|
Reopening as that index as is seems not correct for that query + is missing other important things:
a.) phone: That is an optional field probably not even filled in in many cases -> probably should be partial index
b.) the query uses upper in where clause the index does not -> it will not be used
c.) The query uses startswith search the index does not take that into again again it will not be used |
|
|
|
(0102464)
|
|
hgbot
|
|
2018-02-16 14:15
|
|
|
|
|
|
|
After further discussion, we have decided not to do any change for this issue. The affected customer has a custom index which works for them, and we don't want to include an index which probably won't be useful for the majority of customers, and will add some overhead to all of them. |
|
|
|
|
|
|
|
|
|