Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0010483 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Java Client POS] (No Category) | major | always | 2009-09-04 02:24 | 2014-04-02 09:04 | |||
| Reporter | ronny_g | View Status | public | |||||
| Assigned To | adrianromero | |||||||
| Priority | normal | Resolution | fixed | Fixed in Version | ||||
| Status | closed | Fix in branch | Fixed in SCM revision | fe5c5f8fef63 | ||||
| Projection | none | ETA | none | Target Version | 2.40 | |||
| OS | Windows | Database | Any | Java version | 1.6.0 | |||
| OS Version | Vista | Database version | MySQL 5.0 | Ant version | 1.7.1 | |||
| Product Version | SCM revision | 630 | ||||||
| Regression date | ||||||||
| Regression introduced by commit | ||||||||
| Regression level | ||||||||
| Review Assigned To | ||||||||
| Regression introduced in release | ||||||||
| Summary | 0010483: Close Cash taking to long to execute queries | |||||||
| Description | We 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 Reproduce | See 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. | |||||||
| Tags | No tags attached. | |||||||
| Attached Files |  pos1-slow.log [^] (203,362 bytes) 2009-09-04 02:24 | |||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|
 Relationships |
|
Notes |
|
|
(0019502) cyberjorge (reporter) 2009-09-08 09:30 |
Hi, please note this also occurs in PostgreSQL 8.3 / 8.4 |
|
(0019535) adrianromero (manager) 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 (reporter) 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 (developer) 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 (manager) 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 (manager) 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 (manager) 2014-04-02 09:04 |
Closed as it was in Resolved for too long |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2009-09-04 02:24 | ronny_g | New Issue | |
| 2009-09-04 02:24 | ronny_g | Assigned To | => adrianromero |
| 2009-09-04 02:24 | ronny_g | File Added: pos1-slow.log | |
| 2009-09-04 02:25 | ronny_g | Issue Monitored: ronny_g | |
| 2009-09-06 21:46 | tostinni | Issue Monitored: tostinni | |
| 2009-09-08 09:30 | cyberjorge | Note Added: 0019502 | |
| 2009-09-08 17:27 | adrianromero | Status | new => scheduled |
| 2009-09-08 17:27 | adrianromero | Note Added: 0019535 | |
| 2009-09-08 17:27 | adrianromero | fix_in_branch | => pi |
| 2010-09-17 09:32 | adrianromero | Severity | major => critical |
| 2010-09-17 09:32 | adrianromero | Target Version | => 2.40 |
| 2010-09-17 09:32 | adrianromero | fix_in_branch | pi => |
| 2011-03-29 17:04 | dalsasua | Note Added: 0035345 | |
| 2011-03-29 17:04 | dalsasua | Severity | critical => major |
| 2011-06-03 10:58 | dalsasua | Assigned To | adrianromero => dalsasua |
| 2011-06-03 12:22 | dalsasua | Assigned To | dalsasua => adrianromero |
| 2011-08-11 11:02 | hgbot | Checkin | |
| 2011-08-11 11:02 | hgbot | Note Added: 0040110 | |
| 2011-08-11 11:02 | hgbot | Status | scheduled => resolved |
| 2011-08-11 11:02 | hgbot | Resolution | open => fixed |
| 2011-08-11 11:02 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/pos/devel/pi/rev/fe5c5f8fef63a0d3edc99768d5ea4c1d215d24aa [^] |
| 2011-08-11 11:25 | adrianromero | Note Added: 0040111 | |
| 2011-08-11 11:26 | adrianromero | Note Added: 0040112 | |
| 2012-11-07 09:40 | priyam | Category | 05 - Database => (No Category) |
| 2014-04-02 09:04 | plujan | Note Added: 0066031 | |
| 2014-04-02 09:04 | plujan | Status | resolved => closed |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |