0027632: The query for a querywidget is executed three times

Notes
(0070236) alostale (manager) 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 (manager) 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 (developer) 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 (manager) 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 (administrator) 2014-09-26 17:47	Code reviewed and verified in pi@f5caad158261

(0070452) hudsonbot (developer) 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

Issue History
Date Modified	Username	Field	Change
2014-09-17 20:57	egoitz	New Issue
2014-09-17 20:57	egoitz	Assigned To	=> AugustoMauch
2014-09-17 20:57	egoitz	Modules	=> Core
2014-09-17 20:57	egoitz	Resolution time	=> 1413410400
2014-09-17 20:57	egoitz	Triggers an Emergency Pack	=> No
2014-09-18 11:44	egoitz	Tag Attached: Performance
2014-09-18 12:32	shuehner	Issue Monitored: shuehner
2014-09-22 13:33	alostale	Note Added: 0070236
2014-09-22 13:42	alostale	Relationship added	related to 0026911
2014-09-22 13:43	alostale	Note Edited: 0070236	View Revisions
2014-09-22 16:36	jonalegriaesarte	Target Version	=> 3.0PR15Q1
2014-09-25 12:10	alostale	Assigned To	AugustoMauch => alostale
2014-09-25 12:11	alostale	Note Added: 0070367
2014-09-25 12:21	alostale	Review Assigned To	=> AugustoMauch
2014-09-25 12:21	hgbot	Checkin
2014-09-25 12:21	hgbot	Note Added: 0070368
2014-09-25 12:21	hgbot	Status	new => resolved
2014-09-25 12:21	hgbot	Resolution	open => fixed
2014-09-25 12:21	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/faa80d419e83a2fc7f45e95ea2cb74f6a6f75157 [^]
2014-09-25 12:31	alostale	Note Added: 0070372
2014-09-26 17:47	AugustoMauch	Note Added: 0070441
2014-09-26 17:47	AugustoMauch	Status	resolved => closed
2014-09-26 17:47	AugustoMauch	Fixed in Version	=> 3.0PR14Q4
2014-09-26 19:08	hudsonbot	Checkin
2014-09-26 19:08	hudsonbot	Note Added: 0070452