Bug
Range request takes forever
Issue description
When opening a certain form in a large (> 1 mio topics) system, the screen is grey for longer than 30 seconds. Then the form shows.
Developer comments
This is caused by a topic role constraint TRC1 which pulls in many, many more forms and due to [8643, the refactoring of the range retrieval] all ranges for select boxes are retrieved in one request. TRC1 was only present for the admin so it did not affect other user groups.
What is still interesting that a single range retrieval can take up to 10 seconds. This seems quite a lot. See if you can speed that up.
This is caused by assembling the most likely options. In a large system (in this case more than 5 mio roles), this takes very long. Most of these roles do not matter, since in the end only 7 players are selected, but the sorting of such a large number of roles just takes very long. It should be possible to speed up this process significantly by considering only the last 10.000 roles or so. Only in rare circumstances this would have an effect on the extent.
In general the range retrieval suffers from the problem that can be best be labeled the NOT IN problem. All related queries need to decide whether the instances are frozen and not fully bound. To achieve this, i use NOT IN in the SQL query. Lets assume there is F frozen topics and B fully bound ones. For every candidate there needs F + B comparisons since it should be distinct from all these. This makes it slow. I refactored one of the queries to avoid NOT IN from one single query into three separate ones, cutting it down from 8 seconds to 500 ms.
The range retrieval was refactored and shows improved performance now. In general: a form that can be used without internet connection (after it has been openend), must display all options in select boxes. When there is a form which loads slow it may be because it retrieves all select boxes of possible child forms as well. This can be circumvented by using the extended selection dialog.
Range retrieval is still suboptimal in some situations. When options for a counter player are retrieved where there is a limit on how often it may play the respective role, there is always an expensive SQL statement. In many cases the resulting options are then removed from the list since they are frozen.
How to reproduce
Edit cargo 1135197 as admin.
|
Work sessions8
Start |
2021-08-27T08:53:11
|
End |
2021-08-27T10:48:09
|
Participant |
Robert Cerny
|
Start |
2021-08-27T14:08:30
|
End |
2021-08-27T17:23:42
|
Participant |
Robert Cerny
|
Start |
2021-08-28T07:37:41
|
End |
2021-08-28T09:28:56
|
Participant |
Robert Cerny
|
Start |
2021-08-28T11:06:54
|
End |
2021-08-28T12:38:21
|
Participant |
Robert Cerny
|
Start |
2021-08-29T07:21:26
|
End |
2021-08-29T10:36:35
|
Participant |
Robert Cerny
|
Start |
2021-08-29T16:19:45
|
End |
2021-08-29T17:35:19
|
Participant |
Robert Cerny
|
Start |
2021-08-30T07:16:31
|
End |
2021-08-30T13:16:41
|
Participant |
Robert Cerny
|
Start |
2021-08-30T14:25:30
|
End |
2021-08-30T15:56:56
|
Participant |
Robert Cerny
|
|
We are sorry
This page cannot be displayed in your browser. Use Firefox, Opera, Safari, or Chrome instead.