Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027632Openbravo ERPA. Platformpublic2014-09-17 20:572014-09-26 19:08
Reporteregoitz 
Assigned Toalostale 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR15Q1Fixed in Version3.0PR14Q4 
Merge Request Status
Review Assigned ToAugustoMauch
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027632: The query for a querywidget is executed three times
Descriptionwhen login the application, The query for a querywidgets on the workspaces are executed three times for each widget.
Steps To Reproduce-Enable the query logs on postgresql
-Open an application with a querywidget instance on the home page.
-See that the query is being executed 3 times.
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to defect 0026911 closed AugustoMauch The request to the datasource are done several times when opening a selector from a 2.50 process popup 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-09-17 20:57egoitzNew Issue
2014-09-17 20:57egoitzAssigned To => AugustoMauch
2014-09-17 20:57egoitzModules => Core
2014-09-17 20:57egoitzResolution time => 1413410400
2014-09-17 20:57egoitzTriggers an Emergency Pack => No
2014-09-18 11:44egoitzTag Attached: Performance
2014-09-18 12:32shuehnerIssue Monitored: shuehner
2014-09-22 13:33alostaleNote Added: 0070236
2014-09-22 13:42alostaleRelationship addedrelated to 0026911
2014-09-22 13:43alostaleNote Edited: 0070236bug_revision_view_page.php?bugnote_id=0070236#r6603
2014-09-22 16:36jonalegriaesarteTarget Version => 3.0PR15Q1
2014-09-25 12:10alostaleAssigned ToAugustoMauch => alostale
2014-09-25 12:11alostaleNote Added: 0070367
2014-09-25 12:21alostaleReview Assigned To => AugustoMauch
2014-09-25 12:21hgbotCheckin
2014-09-25 12:21hgbotNote Added: 0070368
2014-09-25 12:21hgbotStatusnew => resolved
2014-09-25 12:21hgbotResolutionopen => fixed
2014-09-25 12:21hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/faa80d419e83a2fc7f45e95ea2cb74f6a6f75157 [^]
2014-09-25 12:31alostaleNote Added: 0070372
2014-09-26 17:47AugustoMauchNote Added: 0070441
2014-09-26 17:47AugustoMauchStatusresolved => closed
2014-09-26 17:47AugustoMauchFixed in Version => 3.0PR14Q4
2014-09-26 19:08hudsonbotCheckin
2014-09-26 19:08hudsonbotNote Added: 0070452

Notes
(0070236)
alostale   
2014-09-22 13:33   
(edited on: 2014-09-22 13:43)
There are several parts to be fixed:

* If "show all" data widget flag is disabled: 2 requests are perfomed generating 3 DB queries (as reported):
  - 1st request with standard pagination 0-75 and rowsNumber parameter as set as parameter -> this executes 2 queries one to get data (up to rowsNumber records) and another one to count records, note this second one has the same limits as first one so useful, not only that but instead of doing count, generates in memory the whole dataset to finally count the objects
  - 2nd request is done to perform the actual count to show the "Showing x of y records" message. This one does the query without any limits. Note instead of returning just the number of records it returns a JSON object with all the data so the client can count number of records

* If "show all data" is enabled or if it is disabled and "show all data" is clicked and the records to be shown in the widget is big enough: several request are done to initially populate the first pages of the widget. To fix this part check fix for 0026911
(0070367)
alostale   
2014-09-25 12:11   
As agreed with DME:

One of the problematic parts is list widgets display an exact record count, which, apart of the current poor implementation, is known can have performance problems.

My proposal is to completely remove this count in the same way we do in standard grids. So in a widget that currently displays "Showing 10 of 25321 records - Show all...", it would now display "Showing 10 of many records - Show all..." (or st similar) instead.
(0070368)
hgbot   
2014-09-25 12:21   
Repository: erp/devel/pi
Changeset: faa80d419e83a2fc7f45e95ea2cb74f6a6f75157
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Sep 25 12:17:25 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/faa80d419e83a2fc7f45e95ea2cb74f6a6f75157 [^]

fixed bug 27632: unneeded DB queries + request in Query List Widget

  * Preventing extra request to get actual count of total number of records which
    is potentially non-perfomant: now a hint of having more records is shown but
    it doesn't include the total number
  * Removed unneeded count in the request to get the actual data. This count caused
    and extra DB query and the number got in that one was incorrect as it was always
    the retrived number of records

---
M modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_MESSAGE.xml
M modules/org.openbravo.client.querylist/web/org.openbravo.client.querylist/js/ob-querylist-widget.js
---
(0070372)
alostale   
2014-09-25 12:31   
Executed test cases:

In an instance with 360K products, created widget with this query: select name as n from Product order by creationDate

-Add Widget and show 5 lines, show all disabled -> 1 request with 1 query, "Showing 10 of many rows" message is displayed in widget
-Maximize widget -> Only one request is executed for 1st page, subsequent queries are executed 1 by one when scrolling down
-Change parameter to show all records -> both in workspace and maximized requests only the visualized pages

Performance improvements:

In workspace not showing all records
 * Before fix:
    -1st request 425Bytes, 424ms (2 DB queries)
    -2nd request 13.1MB, 4.43s (1 DB query)
 * After fix:
    -single request 425Bytes, 187ms (1 DB query)
Maximized or showing all records:
 * Before fix several requests for 1st page
 * After fix single request for 1st page
(0070441)
AugustoMauch   
2014-09-26 17:47   
Code reviewed and verified in pi@f5caad158261
(0070452)
hudsonbot   
2014-09-26 19:08   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/37b816a315f2 [^]
Maturity status: Test