Trying to improve performance with thew Universal GUI where possible, I have encountered the COUNT(*) being a big issue in certain screens as well. Especially when people are filtering using the combined filter.
The way this filter works is already not very performance positive, but having the same call being done again to count the rows, makes the screen feel very sluggish. Certain searches take around 2.5-3.5 (maybe even more) seconds and that gets doubled by the extra call.
I think this call is intended for the paging mechanic (display # max records over all pages) or in combination with a “max. no. of records.” to (not) show the message exceeding result shows message.
Is there already a way to prevent this call from being made for specific views/tables?
In my example this would already gain a 2.5-3.5 seconds waiting for a result to show.
Prevent COUNT(*) calls from happening to increase performance?
Best answer by Arie V
Nevertheless, we did a bit of refinement with the Universal team and came to the below conclusions. Good to know is that the COUNT call is also used to display the number of records that would be exported when pressing ‘Export immediately’ in the overflow menu. With the below proposed changes we remove the number in the overflow menu.
Throwing it out here, so you know what to expect and to verify if this is aligned with your expectations. Let me know if not!
Principles:
- Desired behavior = Pagination in Universal GUI by default
- Desired behavior = Developers should be able to deviate from the default with as little effort as possible
- Desired behavior = reduce HTTP Calls where possible
Scenarios:
Page size | Max. no. of records | Actual records in table | COUNT call? | Expected behavior | Issue |
Null | Ignored | Irrelevant | Yes | Apply Pagination of 100 records per page |
|
X | Ignored | Irrelevant | Yes | Apply Pagination of X records per page |
|
<=0 | Null (Default also null) | Irrelevant | No | Show ALL records | Pagination is applied, this should not happen COUNT call happens, but should not |
<=0 | <=0 | Irrelevant | No | Show ALL records | COUNT call happens, but should not |
<=0 | X | <X | No | Show all records (since they are less than the Max no of records) | COUNT call happens, but should not |
<=0 | X | >=X | No | Show X records Show max_records indicator | COUNT call happens, but should not, determine whether or not to show the max_records indicator using the TOP call |
Desired changes:
- Max no of records = null should NOT apply Pagination if Page size <=0
- Eliminate as much COUNT calls as possible
- Show message indicator_for_reached_max_no_of_records based on the TOP call
- Change translation to '‘The maximum number of visible records has been reached”, this way it doesn't matter whether it is exactly the max or more than the max
- Export immediately: Don't show number of records there
- Export: Perform COUNT call upon opening the Export pop-up
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.