Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027046Openbravo ERPA. Platformpublic2014-07-10 18:592014-08-08 18:53
Reporterjecharri 
Assigned Toshankarb 
PriorityimmediateSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR14Q4 
Merge Request Status
Review Assigned ToAugustoMauch
OBNetwork customerOBPS
Web browserGoogle Chrome
ModulesCore
Support ticket29247
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027046: Product simple selector is executing a query in database without limit
DescriptionProduct simple selector is executing a query in database without limit
Steps To Reproduce-Login as "system" and go to "tables and columns"
-Select table "c_orderline" and column "m_product_id"
-Change the column reference to "OBUISEL selector reference" and set "Product simple" selector
-Stop tomcat
-Edit "log4j.lcf" file to display sql queries to be executed
log4j.logger.org.hibernate.SQL=debug, HB
-Compile
-Start tomcat
-Login as "Admin"
-Go to window "sales order" create a header and press new line
-Click in the selector to display all the products

If you go to eclipse console or openbravo.log file, you will see that at this moment SQL query is executed in database without limit

This is a big performance problem when you have more than 1 million of products.
Proposed Solution
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-07-10 18:59jecharriNew Issue
2014-07-10 18:59jecharriAssigned To => AugustoMauch
2014-07-10 18:59jecharriOBNetwork customer => Yes
2014-07-10 18:59jecharriWeb browser => Google Chrome
2014-07-10 18:59jecharriModules => Core
2014-07-10 18:59jecharriSupport ticket => 29247
2014-07-10 18:59jecharriResolution time => 1405116000
2014-07-10 18:59jecharriTriggers an Emergency Pack => No
2014-07-10 18:59jecharriWeb browserGoogle Chrome => Google Chrome
2014-07-10 18:59jecharriSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=6024#r6024
2014-07-10 19:16AugustoMauchNote Added: 0068582
2014-07-11 08:05shankarbAssigned ToAugustoMauch => shankarb
2014-07-11 08:46shankarbStatusnew => scheduled
2014-07-11 08:46shankarbNote Added: 0068586
2014-07-11 08:46shankarbfix_in_branch => pi
2014-07-11 09:25alostaleTag Attached: Performance
2014-07-11 13:09shankarbReview Assigned To => AugustoMauch
2014-07-11 13:09shankarbWeb browserGoogle Chrome => Google Chrome
2014-07-11 13:09shankarbfix_in_branchpi =>
2014-07-11 13:09shankarbIssue Monitored: AugustoMauch
2014-07-11 13:10hgbotCheckin
2014-07-11 13:10hgbotNote Added: 0068591
2014-07-11 13:10hgbotStatusscheduled => resolved
2014-07-11 13:10hgbotResolutionopen => fixed
2014-07-11 13:10hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d81b1159aded47b55a410419fbcce1c5c807111f [^]
2014-07-14 09:57AugustoMauchNote Added: 0068598
2014-07-14 09:57AugustoMauchStatusresolved => closed
2014-07-14 09:57AugustoMauchFixed in Version => PR14Q4
2014-08-08 18:53hudsonbotCheckin
2014-08-08 18:53hudsonbotNote Added: 0069152

Notes
(0068582)
AugustoMauch   
2014-07-10 19:16   
This is reproducible with all Custom Query selectors.

The query is done twice, first without a limit, then with a limit:

select businesspa0_.Name as col_0_0_, businesspa0_.C_BPartner_ID as col_1_0_, businesspa0_.Value as col_2_0_, businesspa0_.SO_CreditLimit-businesspa0_.SO_CreditUsed as col_3_0_, businesspa0_.SO_CreditUsed as col_4_0_, businesspa0_.IsCustomer as col_5_0_, businesspa0_.IsVendor as col_6_0_, businesspa1_.Name as col_7_0_ from C_BPartner businesspa0_, C_BP_Group businesspa1_ where businesspa0_.C_BP_Group_ID=businesspa1_.C_BP_Group_ID and (businesspa0_.AD_Client_ID in ('0' , '7273232E589944E3B2C314CCFA24CE51')) and (businesspa0_.AD_Org_ID in ('93026108D6314843BDAF1E2C9582029E' , '0' , 'C9040AD8D77642D98F89A5201E1EE1F5' , '77A6A6E722384AF884E80AC54D0E9A14')) and businesspa0_.IsCustomer='Y' and businesspa0_.IsSummary='N' and businesspa0_.IsActive='Y' order by 1

select businesspa0_.Name as col_0_0_, businesspa0_.C_BPartner_ID as col_1_0_, businesspa0_.Value as col_2_0_, businesspa0_.SO_CreditLimit-businesspa0_.SO_CreditUsed as col_3_0_, businesspa0_.SO_CreditUsed as col_4_0_, businesspa0_.IsCustomer as col_5_0_, businesspa0_.IsVendor as col_6_0_, businesspa1_.Name as col_7_0_ from C_BPartner businesspa0_, C_BP_Group businesspa1_ where businesspa0_.C_BP_Group_ID=businesspa1_.C_BP_Group_ID and (businesspa0_.AD_Client_ID in ('0' , '7273232E589944E3B2C314CCFA24CE51')) and (businesspa0_.AD_Org_ID in ('93026108D6314843BDAF1E2C9582029E' , '0' , 'C9040AD8D77642D98F89A5201E1EE1F5' , '77A6A6E722384AF884E80AC54D0E9A14')) and businesspa0_.IsCustomer='Y' and businesspa0_.IsSummary='N' and businesspa0_.IsActive='Y' order by 1 limit ?

The unlimited query is done here [1], to calculate the total number of rows.

[1] https://code.openbravo.com/erp/devel/pi/file/8b0eab71cd09/modules/org.openbravo.userinterface.selector/src/org/openbravo/userinterface/selector/CustomQuerySelectorDatasource.java#l113 [^]
(0068586)
shankarb   
2014-07-11 08:46   
fix pushed to try.
(0068591)
hgbot   
2014-07-11 13:10   
Repository: erp/devel/pi
Changeset: d81b1159aded47b55a410419fbcce1c5c807111f
Author: Shankar Balachandran <shankar.balachandran <at> openbravo.com>
Date: Fri Jul 11 12:14:31 2014 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/d81b1159aded47b55a410419fbcce1c5c807111f [^]

Fixes Issue 27046: Product simple selector is executing a query in database without limit

When data is fetched in custom query selector the data was iterated to get the number of rows.
This operation is avoided and the total rows is set based on the resultSet iterated later.

---
M modules/org.openbravo.userinterface.selector/src/org/openbravo/userinterface/selector/CustomQuerySelectorDatasource.java
---
(0068598)
AugustoMauch   
2014-07-14 09:57   
Code reviewed and verified in pi@119c05bb76af
(0069152)
hudsonbot   
2014-08-08 18:53   
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/4450016dee64 [^]
Maturity status: Test