Openbravo Issue Tracking System - Java Client POS
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0010483Java Client POS(No Category)public2009-09-04 02:242014-04-02 09:04
Reporterronny_g 
Assigned Toadrianromero 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS10OS VersionVista
Product Version 
Target Version2.40Fixed in Version 
Regression date
Regression introduced by commit
Regression level
Review Assigned To
OBNetwork customer
Support ticket
Regression introduced in release
Summary0010483: Close Cash taking to long to execute queries
DescriptionWe are having problems where closing the cash seems to be taking longer and longer (now approx 2mins).

This was not the case when we first started out with OBPOS.
We have been running for about 12 months.

MySQL shows: 472,000 + rows, Data Len 256.6MB, Index Len. 149.7MB.

The PC is an AMD Sempron 3400+1.81GHz, 1GB RAM.

The problem is, we like to print a Close Cash ticket in the middle of the days trade. If the customers come up to pay, they have to then wait while the PC is still processing.

Even after the first 10 orders in the morning and then pressing Close Cash, it is taking forever.

Optimising tables helped for a short while but times have grown out again to 3+ mins and still growing.
Steps To ReproduceSee this thread for a full report:
http://forge.openbravo.com/plugins/espforum/view.php?group_id=101&forumid=434921&topicid=6999500&topid=7001665 [^]

See attached log file for the latest slow query report.
You can see that the query time has slowly grown since 31/7/09.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Fileslog pos1-slow.log (203,362) 2009-09-04 02:24
https://issues.openbravo.com/file_download.php?file_id=1710&type=bug
Issue History
Date ModifiedUsernameFieldChange
2009-09-04 02:24ronny_gNew Issue
2009-09-04 02:24ronny_gAssigned To => adrianromero
2009-09-04 02:24ronny_gFile Added: pos1-slow.log
2009-09-04 02:25ronny_gIssue Monitored: ronny_g
2009-09-06 21:46tostinniIssue Monitored: tostinni
2009-09-08 09:30cyberjorgeNote Added: 0019502
2009-09-08 17:27adrianromeroStatusnew => scheduled
2009-09-08 17:27adrianromeroNote Added: 0019535
2009-09-08 17:27adrianromerofix_in_branch => pi
2010-09-17 09:32adrianromeroSeveritymajor => critical
2010-09-17 09:32adrianromeroTarget Version => 2.40
2010-09-17 09:32adrianromerofix_in_branchpi =>
2011-03-29 17:04dalsasuaNote Added: 0035345
2011-03-29 17:04dalsasuaSeveritycritical => major
2011-06-03 10:58dalsasuaAssigned Toadrianromero => dalsasua
2011-06-03 12:22dalsasuaAssigned Todalsasua => adrianromero
2011-08-11 11:02hgbotCheckin
2011-08-11 11:02hgbotNote Added: 0040110
2011-08-11 11:02hgbotStatusscheduled => resolved
2011-08-11 11:02hgbotResolutionopen => fixed
2011-08-11 11:02hgbotFixed in SCM revision => http://code.openbravo.com/pos/devel/pi/rev/fe5c5f8fef63a0d3edc99768d5ea4c1d215d24aa [^]
2011-08-11 11:25adrianromeroNote Added: 0040111
2011-08-11 11:26adrianromeroNote Added: 0040112
2012-11-07 09:40priyamCategory05 - Database => (No Category)
2014-04-02 09:04plujanNote Added: 0066031
2014-04-02 09:04plujanStatusresolved => closed

Notes
(0019502)
cyberjorge   
2009-09-08 09:30   
Hi, please note this also occurs in PostgreSQL 8.3 / 8.4
(0019535)
adrianromero   
2009-09-08 17:27   
Hi

For sure these SQL sentences have to be optimized because it does not have to take more than 3 minutes and explore all that amount of records to get the results.

I have been doing a quick review of the definitions of these tables and I do not find what the problem can be because all the fields used in the filter have an index defined and should run faster. More investigation is needed.
(0035345)
dalsasua   
2011-03-29 17:04   
Reduced the severity, according to our reporting guidelines http://wiki.openbravo.com/wiki/Bug_Reporting_Guidelines#How_to_Choose_the_Right_Severity [^]
(0040110)
hgbot   
2011-08-11 11:02   
Repository: pos/devel/pi
Changeset: fe5c5f8fef63a0d3edc99768d5ea4c1d215d24aa
Author: Adrián Romero <adrianromero <at> openbravo.com>
Date: Thu Aug 11 11:00:03 2011 +0200
URL: http://code.openbravo.com/pos/devel/pi/rev/fe5c5f8fef63a0d3edc99768d5ea4c1d215d24aa [^]

Fixes issue 0010483: Close Cash taking to long to execute queries
It has been added three indexes to optimize the execution of SQL queries in Close Cash window

---
M src-pos/com/openbravo/pos/scripts/Derby-create.sql
M src-pos/com/openbravo/pos/scripts/HSQLDB-create.sql
M src-pos/com/openbravo/pos/scripts/MySQL-create.sql
M src-pos/com/openbravo/pos/scripts/Oracle-create.sql
M src-pos/com/openbravo/pos/scripts/PostgreSQL-create.sql
---
(0040111)
adrianromero   
2011-08-11 11:25   
* Testing the issue

There is needed first to fill in the database with more than 10000 receipts. Go to the Close cash panel and verify that it does not take a significant time to load the data.

It also can be tested executing the plan table of the sql sentences executed in the close cash panel:

SELECT COUNT(*), SUM(PAYMENTS.TOTAL) FROM PAYMENTS, RECEIPTS WHERE PAYMENTS.RECEIPT = RECEIPTS.ID AND RECEIPTS.MONEY = '5f7e0e6e-7e89-461b-8b9e-017476ffaad4'
SELECT PAYMENTS.PAYMENT, SUM(PAYMENTS.TOTAL) FROM PAYMENTS, RECEIPTS WHERE PAYMENTS.RECEIPT = RECEIPTS.ID AND RECEIPTS.MONEY = '5f7e0e6e-7e89-461b-8b9e-017476ffaad4' GROUP BY PAYMENTS.PAYMENT
SELECT COUNT(DISTINCT RECEIPTS.ID), SUM(TICKETLINES.UNITS * TICKETLINES.PRICE) FROM RECEIPTS, TICKETLINES WHERE RECEIPTS.ID = TICKETLINES.TICKET AND RECEIPTS.MONEY = '5f7e0e6e-7e89-461b-8b9e-017476ffaad4'
SELECT SUM(TAXLINES.AMOUNT) FROM RECEIPTS, TAXLINES WHERE RECEIPTS.ID = TAXLINES.RECEIPT AND RECEIPTS.MONEY = '5f7e0e6e-7e89-461b-8b9e-017476ffaad4'
SELECT TAXCATEGORIES.NAME, SUM(TAXLINES.AMOUNT) FROM RECEIPTS, TAXLINES, TAXES, TAXCATEGORIES WHERE RECEIPTS.ID = TAXLINES.RECEIPT AND TAXLINES.TAXID = TAXES.ID AND TAXES.CATEGORY = TAXCATEGORIES.ID AND RECEIPTS.MONEY = '5f7e0e6e-7e89-461b-8b9e-017476ffaad4'GROUP BY TAXCATEGORIES.NAME



* Other areas affected

No other areas affected only the close cash panel.
(0040112)
adrianromero   
2011-08-11 11:26   
To apply the solution on an existing Openbravo POS deployment is as simple as add the following indexes to the database:

CREATE INDEX RECEIPTS_MONEY_INX ON RECEIPTS(MONEY);
CREATE INDEX PAYMENTS_RECEIPT_INX ON PAYMENTS(RECEIPT);
CREATE INDEX TAXLINES_RECEIPT_INX ON TAXLINES(RECEIPT);
(0066031)
plujan   
2014-04-02 09:04   
Closed as it was in Resolved for too long