0030284: slow query for orders

Description

While 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 Reproduce

In webpos search for existing tickets (preferably on a large database).

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

blocks

defect

0029543

RR15Q3

closed

aaroncalero

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 (viewer) 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