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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0029543
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2015-04-09 10:222015-06-29 19:17
ReportermtaalView Statuspublic 
Assigned Toaaroncalero 
PriorityurgentResolutionfixedFixed in VersionRR15Q3
StatusclosedFix in branchFixed in SCM revision20a372dba994
ProjectionnoneETAnoneTarget VersionRR15Q3
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomigueldejuana
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0029543: 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).
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 0030284RR15Q2.3 closedaaroncalero slow query for orders 

-  Notes
(0077870)
hgbot (developer)
2015-05-28 15:25

 Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 20a372dba994513e44898a291186d87fca31174c
Author: Aaron Calero <aaron.calero <at> openbravo.com>
Date: Thu May 28 10:17:40 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/20a372dba994513e44898a291186d87fca31174c [^]

Fixed issue 29543: slow query for orders

Replaced two count subqueries with exist subqueries

---
M src/org/openbravo/retail/posterminal/PaidReceiptsHeader.java
---
(0077953)
migueldejuana (developer)
2015-06-01 18:19

 Tested and reviewed in a5c25afa2d03

- Issue History
Date Modified Username Field Change
2015-04-09 10:22 mtaal New Issue
2015-04-09 10:22 mtaal Assigned To => mtaal
2015-04-09 10:22 mtaal Triggers an Emergency Pack => No
2015-04-13 18:00 shuehner Tag Attached: Performance
2015-04-23 17:46 jonalegriaesarte Resolution time => 1432332000
2015-04-23 17:46 jonalegriaesarte Priority normal => urgent
2015-04-28 15:55 shuehner Issue Monitored: shuehner
2015-05-26 17:47 aaroncalero Assigned To mtaal => aaroncalero
2015-05-28 15:25 hgbot Checkin
2015-05-28 15:25 hgbot Note Added: 0077870
2015-05-28 15:25 hgbot Status new => resolved
2015-05-28 15:25 hgbot Resolution open => fixed
2015-05-28 15:25 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/20a372dba994513e44898a291186d87fca31174c [^]
2015-06-01 18:19 migueldejuana Review Assigned To => migueldejuana
2015-06-01 18:19 migueldejuana Note Added: 0077953
2015-06-01 18:19 migueldejuana Status resolved => closed
2015-06-01 18:19 migueldejuana Fixed in Version => RR15Q3
2015-06-29 19:17 marvintm Status closed => new
2015-06-29 19:17 marvintm Resolution fixed => open
2015-06-29 19:17 marvintm Fixed in Version RR15Q3 =>
2015-06-29 19:17 marvintm Status new => scheduled
2015-06-29 19:17 marvintm Status scheduled => resolved
2015-06-29 19:17 marvintm Resolution open => fixed
2015-06-29 19:17 marvintm Status resolved => closed
2015-06-29 19:17 marvintm Fixed in Version => RR15Q3

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker