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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0030284
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Retail Modules] Web POSmajorhave not tried2015-04-09 10:222015-07-27 13:59
ReportermtaalView Statuspublic 
Assigned Toaaroncalero 
PriorityurgentResolutionfixedFixed in VersionRR15Q2.3
StatusclosedFix in branchFixed in SCM revision403794a75efb
ProjectionnoneETAnoneTarget VersionRR15Q2.3
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toguilleaer
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0030284: slow query for orders

DescriptionWhile doing a system sizing guideline a slow query in WebPOS was encountered (on the server) which was particurarly slow, and easy to make faster [1].

Several things where noted:
- several queries are fired after eachother, so there seems to be no delay while the user types (so not related to the sql, but still wanted to mention it)
- the query has 2 like clauses, only the one replacing the \ is enough
- like clauses with a percentage as a start will never use an index
- where clauses with functions will never use an index (hopefully as part of the store server project this can be changed)
- this subquery showed to have a great impact:
(select count(orderlinel3_.QtyDelivered) from C_OrderLine orderlinel3_ where order0_.C_Order_ID=orderlinel3_.C_Order_ID and orderlinel3_.QtyDelivered<>0)>0
changing it to:
exists (select 1 from C_OrderLine orderlinel3_ where order0_.C_Order_ID=orderlinel3_.C_Order_ID and orderlinel3_.QtyDelivered<>0)
makes it the whole query more than 10 times faster! The query went from 6.5 seconds to 500 millis
- also there are 2 count subqueries, would only querying on delivered quantity be enough?

I will enter an issue for it.

gr. Martin

[1]
2015-04-08 16:01:17 CAT [1339-81] tad_halsteds@halsted_live LOG: duration: 6652.366 ms execute <unnamed>: select order0_.C_Order_ID as col_0_0_, order0_.DocumentNo as col_1_0_, order0_.DateOrdered as col_2_0_, businesspa1_.Name as col_3_0_, order0_.GrandTotal as col_4_0_, order0_.C_DocType_ID as col_5_0_, 'false' as col_6_0_ from C_Order order0_, C_BPartner businesspa1_ where order0_.C_BPartner_ID=businesspa1_.C_BPartner_ID and order0_.AD_Client_ID='7B27EAB5E42841E0A0957AF0B16CCB3B' and order0_.AD_Org_ID='2D164FEBDD474277B5AE9858AD9CBA01' and (order0_.EM_Obpos_Applications_ID is not null) and (order0_.DocumentNo like '%B42%' or REPLACE(order0_.DocumentNo, '/', '') like '%B42%' or upper(businesspa1_.Name) like upper('%B42%')) and (order0_.C_DocType_ID='040B0B064AE54A848954C34D902F36D9' or order0_.C_DocType_ID='ABECD2CF89A64FDF90F9B7180FDAD44B') and (select count(orderlinel3_.QtyDelivered) from C_OrderLine orderlinel3_ where order0_.C_Order_ID=orderlinel3_.C_Order_ID and orderlinel3_.QtyDelivered<>0)>0 and (select count(orderlinel4_.QtyOrdered) from C_OrderLine orderlinel4_ where order0_.C_Order_ID=orderlinel4_.C_Order_ID and orderlinel4_.QtyOrdered>0)>0 order by order0_.DateOrdered desc, order0_.DocumentNo desc limit $1
2015-04-08 16:01:17 CAT [1339-82] tad_halsteds@halsted_live DETAIL: parameters: $1 = '300'
[22:35:34] Stefan Huehner: 6.6s: grid query ilike %b42%
Steps To ReproduceIn webpos search for existing tickets (preferably on a large database).
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
blocks defect 0029543RR15Q3 closedaaroncalero slow query for orders 

-  Notes
(0078855)
hgbot (developer)
2015-07-16 11:42

Repository: retail/backports/3.0RR15Q2.3/org.openbravo.retail.posterminal
Changeset: 403794a75efbac818ecf6812c3a856f324bd2e1e
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Thu Jul 16 11:42:05 2015 +0200
URL: http://code.openbravo.com/retail/backports/3.0RR15Q2.3/org.openbravo.retail.posterminal/rev/403794a75efbac818ecf6812c3a856f324bd2e1e [^]

Fixed issue 30284. slow query for orders

Replaced two count subqueries with exist subqueries

---
M src/org/openbravo/retail/posterminal/PaidReceiptsHeader.java
---
(0079078)
guilleaer (developer)
2015-07-27 13:59

verified and closed

- Issue History
Date Modified Username Field Change
2015-06-29 19:17 marvintm Type defect => backport
2015-06-29 19:17 marvintm Target Version RR15Q3 => RR15Q2.3
2015-07-16 11:42 hgbot Checkin
2015-07-16 11:42 hgbot Note Added: 0078855
2015-07-16 11:42 hgbot Status scheduled => resolved
2015-07-16 11:42 hgbot Resolution open => fixed
2015-07-16 11:42 hgbot Fixed in SCM revision http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/20a372dba994513e44898a291186d87fca31174c [^] => http://code.openbravo.com/retail/backports/3.0RR15Q2.3/org.openbravo.retail.posterminal/rev/403794a75efbac818ecf6812c3a856f324bd2e1e [^]
2015-07-27 13:59 guilleaer Review Assigned To migueldejuana => guilleaer
2015-07-27 13:59 guilleaer Note Added: 0079078
2015-07-27 13:59 guilleaer Status resolved => closed
2015-07-27 13:59 guilleaer Fixed in Version => RR15Q2.3


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker