0029543: slow query for orders - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Retail Modules

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0029543

Retail Modules

Web POS

public

2015-04-09 10:22

2015-06-29 19:17

Reporter

mtaal

Assigned To

aaroncalero

Priority

urgent

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

RR15Q3

Fixed in Version

RR15Q3

Merge Request Status

Review Assigned To

migueldejuana

OBNetwork customer

OBPS

Support ticket

35812

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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

Proposed Solution

Additional Information