{"action":"created","issue":{"url":"https://api.github.com/repos/UnifiedViews/Core/issues/544","labels_url":"https://api.github.com/repos/UnifiedViews/Core/issues/544/labels{/name}","comments_url":"https://api.github.com/repos/UnifiedViews/Core/issues/544/comments","events_url":"https://api.github.com/repos/UnifiedViews/Core/issues/544/events","html_url":"https://github.com/UnifiedViews/Core/issues/544","id":113604042,"number":544,"title":"Pipeline list and execution list views refresh very inefficient and slow","user":{"login":"eea03","id":10461994,"avatar_url":"https://avatars.githubusercontent.com/u/10461994?v=3","gravatar_id":"","url":"https://api.github.com/users/eea03","html_url":"https://github.com/eea03","followers_url":"https://api.github.com/users/eea03/followers","following_url":"https://api.github.com/users/eea03/following{/other_user}","gists_url":"https://api.github.com/users/eea03/gists{/gist_id}","starred_url":"https://api.github.com/users/eea03/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eea03/subscriptions","organizations_url":"https://api.github.com/users/eea03/orgs","repos_url":"https://api.github.com/users/eea03/repos","events_url":"https://api.github.com/users/eea03/events{/privacy}","received_events_url":"https://api.github.com/users/eea03/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/UnifiedViews/Core/labels/priority:%20High","name":"priority: High","color":"fad8c7"},{"url":"https://api.github.com/repos/UnifiedViews/Core/labels/severity:%20bug","name":"severity: bug","color":"fc2929"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-10-27T14:58:12Z","updated_at":"2015-11-09T14:25:40Z","closed_at":null,"body":"During testing on our development environment, we have discovered that refreshing of pipelines and executions lists views is very inefficient. There are several reasons for this:\r\n\r\n1. changed pipelines / executions test in database is no table entries specific - it checks in database if some pipeline / execution has been changed since last refresh but regardless if should or should not be seen in table\r\n\r\n2. When change / delete is detected, ItemSetChangeEvent is generated so all the entries in the table are refreshed. \r\n\r\n3. refresh of containter generates tons of selects - it gets the pipelines and executions individually by getPipeline() and getExecution() service methods. Even worse, the counts of selects cannot be reasonably calculated. E.g. for 60 pipelines, refresh generated 266 selects. This was constant for each refresh while count of pipelines remained the same. When pipeline count increased, so did the count of selects. For executions, the count of selects is significantly lower even when there are more executions, but still quite high (100 selects for 120 executions). And the count of selects clearly depends also on page size of table. Mentioned values were with 20 page size, when i decreased to 10, also count of selects decreased to half\r\n\r\nOn environments with large amount of data, especially when database is on other server, as we already have seen on our test environment.\r\n\r\nFrom what I read, I think there are 2 possible ways of fixing this:\r\n* use ValueChangeEvents instead of ItemSetChangeEvent - this way only really changed items would have been polled from db and refreshed in view. Disadvantage of this is that refresher would have to identify all changes in db and create change events from them\r\n*  use service class to poll table entries in one select - removing the large number of rubish selects\r\n\r\nGood start is to read:\r\n* https://vaadin.com/forum/#!/thread/1095125/7571277\r\n* https://vaadin.com/forum/#!/thread/408555/408554\r\n\r\nThis is quite a complex problem and requires further deep examination"},"comment":{"url":"https://api.github.com/repos/UnifiedViews/Core/issues/comments/155077847","html_url":"https://github.com/UnifiedViews/Core/issues/544#issuecomment-155077847","issue_url":"https://api.github.com/repos/UnifiedViews/Core/issues/544","id":155077847,"user":{"login":"eea03","id":10461994,"avatar_url":"https://avatars.githubusercontent.com/u/10461994?v=3","gravatar_id":"","url":"https://api.github.com/users/eea03","html_url":"https://github.com/eea03","followers_url":"https://api.github.com/users/eea03/followers","following_url":"https://api.github.com/users/eea03/following{/other_user}","gists_url":"https://api.github.com/users/eea03/gists{/gist_id}","starred_url":"https://api.github.com/users/eea03/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eea03/subscriptions","organizations_url":"https://api.github.com/users/eea03/orgs","repos_url":"https://api.github.com/users/eea03/repos","events_url":"https://api.github.com/users/eea03/events{/privacy}","received_events_url":"https://api.github.com/users/eea03/received_events","type":"User","site_admin":false},"created_at":"2015-11-09T14:25:40Z","updated_at":"2015-11-09T14:25:40Z","body":"So after further thorrow testing, it was discovered that this large amount of selects was generated during creating tables - more exactly checking permissions for action buttons\r\n\r\nThis means that in pipeline view, there are 6 checks and each tries to load the pipeline, that is 6 selects for 1 table row. When table has 20 rows, this means 6 * 20 = 120 selects every time table is refreshed (which is very often as this is triggered by any change in the database)\r\n\r\nSame thing for executions - 5 checks --> 5 * 20 = 100 selects every time table is refreshed. (again very often)\r\n\r\nFor scheduler view, situation sligtly differs but still there are redundant selects."}}