Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0037633
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajoralways2018-01-14 09:512018-02-22 18:19
ReporterumartirenaView Statuspublic 
Assigned Torqueralta 
PrioritynormalResolutionno change requiredFixed in Version
StatusclosedFix in branchFixed in SCM revision8d0fbb9abd2e
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomarvintm
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0037633: Search a Business Partner by 'Phone' in 'Remote for Customer' mode raises a JS error with a big amount of customers

DescriptionSearch 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 ReproduceThe 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 SolutionAdding 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");
TagsPerformance
Attached Filespng file icon Selection_124.png [^] (360,408 bytes) 2018-01-14 09:51

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0102453)
hgbot (developer)
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
---
(0102459)
shuehner (administrator)
2018-02-16 12:22

 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 (developer)
2018-02-16 14:15

 Repository: erp/devel/pi
Changeset: 0f81468c4048d86f0e609a2fb7326bc62e19181a
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Fri Feb 16 14:15:32 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/0f81468c4048d86f0e609a2fb7326bc62e19181a [^]

Related to issue 37633. Removed unneeded index

---
M src-db/database/model/tables/AD_USER.xml
---
(0102465)
marvintm (manager)
2018-02-16 14:21

 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.
(0102771)
hudsonbot (developer)
2018-02-22 18:19

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/980a6ad5bbf5 [^]
Maturity status: Test
(0102774)
hudsonbot (developer)
2018-02-22 18:19

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/980a6ad5bbf5 [^]
Maturity status: Test

- 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 Triggers an Emergency Pack => No
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

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker