Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033625Retail ModulesWeb POSpublic2016-08-04 14:172016-08-18 09:18
Reportershuehner 
Assigned Tomtaal 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Tomigueldejuana
OBNetwork customerNo
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033625: (Order|Invoice|Cahsup)ImportEntryProcessor countEntries has bad performance with many rows in c_import_entry
DescriptionThose processors do some functional pre-checks to delay processing of a row if some related rows are still pending to be processed.

I.e. delay cashup until all 'its' orders+invoices have been processed.

The current code forces an exact select count(*) of all pending rows when it only needs 'are there any rows'.

By rewriting that code the query can be made a lot faster by letting the db stop processing after a single matching row has been found.
Steps To ReproduceCurrent code:
select count(*) from C_IMPORT_ENTRY c_import_e0_ where c_import_e0_.ImportStatus='Initial' and (c_import_e0_.Typeofdata='Order' or c_import_e0_.Typeofdata='OBPOS_Invoice' or c_import_e0_.Typeofdata='FIN_Finacc_Transaction' or c_import_e0_.Typeofdata='OBPOS_App_Cashup') and c_import_e0_.Created<now() and c_import_e0_.EM_Obpos_Applications_ID='73B564E1CE044A618E033BD5EF636F46' and c_import_e0_.C_IMPORT_ENTRY_ID<>'E1C1635'

Explain analyze of postgres with db having huge import entry backlog:
 Limit (cost=820959.69..820959.70 rows=1 width=0) (actual time=548.171..548.171 rows=1 loops=1)

===================
new code
select 1 from C_IMPORT_ENTRY c_import_e0_ where c_import_e0_.ImportStatus='Initial' and (c_import_e0_.Typeofdata='Order' or c_import_e0_.Typeofdata='OBPOS_Invoice' or c_import_e0_.Typeofdata='FIN_Finacc_Transaction' or c_import_e0_.Typeofdata='OBPOS_App_Cashup') and c_import_e0_.Created<now() and c_import_e0_.EM_Obpos_Applications_ID='73B564E1CE044A618E033BD5EF636F46' and c_import_e0_.C_IMPORT_ENTRY_ID<>'E1C1635' limit 1;
Explain in same env:
 Limit (cost=0.00..2.28 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=1)
Proposed Solution
Additional Information
TagsPerformance
Relationships
Attached Filesdiff 33625_prototype.diff (3,689) 2016-08-04 14:18
https://issues.openbravo.com/file_download.php?file_id=9677&type=bug
diff 33625.diff (6,962) 2016-08-07 12:01
https://issues.openbravo.com/file_download.php?file_id=9688&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-08-04 14:17shuehnerNew Issue
2016-08-04 14:17shuehnerAssigned To => Retail
2016-08-04 14:17shuehnerOBNetwork customer => No
2016-08-04 14:17shuehnerTriggers an Emergency Pack => No
2016-08-04 14:18shuehnerTag Attached: Performance
2016-08-04 14:18shuehnerFile Added: 33625_prototype.diff
2016-08-04 14:20shuehnerNote Added: 0088820
2016-08-07 10:08mtaalAssigned ToRetail => mtaal
2016-08-07 12:01mtaalFile Added: 33625.diff
2016-08-08 12:43hgbotCheckin
2016-08-08 12:43hgbotNote Added: 0088883
2016-08-08 12:43hgbotStatusnew => resolved
2016-08-08 12:43hgbotResolutionopen => fixed
2016-08-08 12:43hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/99c6f8f71b500fcb1ea967aba5bccbccefe07bc9 [^]
2016-08-08 12:44mtaalReview Assigned To => migueldejuana
2016-08-18 09:18migueldejuanaNote Added: 0089200
2016-08-18 09:18migueldejuanaStatusresolved => closed

Notes
(0088820)
shuehner   
2016-08-04 14:20   
Note: very first part of the patch adding new filter condition is an unrelated fix (deadlock avoidance + functional fix after addition of separate InvoiceLoader (apart from OrderLoader))
Not to be committed in there but should reach code in parallel already.
(0088883)
hgbot   
2016-08-08 12:43   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 99c6f8f71b500fcb1ea967aba5bccbccefe07bc9
Author: Martin Taal <martin.taal <at> openbravo.com>
Date: Mon Aug 08 12:43:01 2016 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/99c6f8f71b500fcb1ea967aba5bccbccefe07bc9 [^]

Fixes issue 33625: (Order|Invoice|Cahsup)ImportEntryProcessor countEntries has bad performance with many rows in c_import_entry
Replace the count query with a select 1 with check on size of result in several retail import entry processors. Select 1
is more performant than count(*).

---
M src/org/openbravo/retail/posterminal/importprocess/CashManagementImportEntryProcessor.java
M src/org/openbravo/retail/posterminal/importprocess/CashUpImportEntryProcessor.java
M src/org/openbravo/retail/posterminal/importprocess/CustomerAddrImportEntryProcessor.java
M src/org/openbravo/retail/posterminal/importprocess/OrderImportEntryProcessor.java
M src/org/openbravo/retail/posterminal/importprocess/QuotationsRejectEntryProcessor.java
M src/org/openbravo/retail/posterminal/importprocess/VoidLayawayEntryProcessor.java
---
(0089200)
migueldejuana   
2016-08-18 09:18   
Verified