Enhancement: Auto switch to progressiveLoading:true in a grouped ListGrid in certain cases

[Blama]

Hi Isomorphic,

I just noticed that we are running unnecessary COUNT(*) queries for a long time now.
Please see this testcase, where dsListGridNoPL is what we have now.
Because of the use of sortByGroupFirst:true the result is thrown away client-side and data re-requested, once the result has more than 1000 records.

So either we have below 1000 records or new data is requested.
In this case the COUNT(*) is useless.

This opens two enhancements:

• maxAcceptedRecords (defaults to groupByMaxRecords): If the count returns more than this number, the normal query is not executed, the client informed about the situation and the ListGrid will automatically request again ungrouped (this requires a new attribute in your request flow and potentially bigger changes to ListGrid, so you might dislike this idea)
• when a group LG is using sortByGroupFirst:true, automatically set progressiveLoading:true

The latter is easy and will solve the problem of the unnecessary COUNT(*) query in the 1st Request of dsListGridNoPL in this testcase (v12.0p_2019-07-11):

isc.ListGrid.create({
    ID:"dsListGridPL",
    width: "100%",
    height: 300,
    minFieldWidth:80,
    autoFetchData: true,
    dataSource: "supplyItem",
    groupByField: 'units',
    sortByGroupFirst: true,
    sortField: "SKU",
    showFilterEditor: true,
    progressiveLoading:true,
    groupByMaxRecords: 1000
});

isc.ListGrid.create({
    ID:"dsListGridNoPL",
    width: "100%",
    height: 300,
    top: 310,
    minFieldWidth:80,
    autoFetchData: true,
    dataSource: "supplyItem",
    groupByField: 'units',
    sortByGroupFirst: true,
    sortField: "SKU",
    showFilterEditor: true,
    groupByMaxRecords: 1000
});

Generally speaking I have almost everywhere used progressiveLoading:false, as it is the default and results in a nice UI (perfect fitting scroll bar). But it is also very expansive if you have complicated fetches, so it might be a good idea to change the default to false, at least for me. If you don't want to change the default which I assume, I do think that a huge notice in the QuickStartGuide might be a good thing here, because it will make applications created with the framework faster.

Best regards
Blama

[Isomorphic]

So to summarize, we're exploring the corner case of (initial grouping + sortByGroupFirst + mismatched sort between grouping and non-grouping + data > maxGroupByRecords). In this case, the requests are:

1. rows 0-1000 with sorting by grouped field (units)

2. rows 0-75 with sort on another field (SKU)

In this scenario, the entire result from #1 has to be thrown away, because it's partial data and its sort differs from how we're configured to sort if grouping is disabled.

There are two possible optimizations:

1. Avoiding returning those 1000 rows in the first response, which are just discarded in this corner case, through a new dsRequest attribute like maxAcceptedRecords, which would cause the server to never retrieve actual rows if the row count is too high

2. Avoiding asking for a row count in the second request since we already know it from the first request, by setting dsRequest.progressiveLoading

First of all, you could avoid the whole situation by specifying:

    initialSort: [{property:"units"},{property:"SKU"}],

.. which makes sorting agree between grouped and non-grouped modes. This is probably better UE as well, as the ungrouped data will be arranged more similarly to the grouped data.

Second, from a UE perspective, we would generally recommend against using initial grouping unless you know the data is going to be small enough to allowing grouping. Having a view come up grouped sometimes but not others can be surprising for end users. Even if the situation is highly dynamic (eg users can configure grids and save the settings), you still have various possibilities to avoid this double request situation:

1. do a row-count-only query before deciding whether to turn on initial grouping (not perfect, but better)
2. automatically adjust the initialSort to match the grouped sort

Note also that optimization #1 (avoiding actual data being returned) could also be implemented in application code, as a special operationBinding that heuristically detects the grouped query, and in server code, issues a count-only dsRequest followed by a progressiveLoading dsRequest if the count is low enough, otherwise returning a dsResponse with only row count information included.

With all of the above taken into account (extreme corner case, easy to fix via initialSort, iffy UE, other options), while we can note down these possible framework-level optimizations, it's a safe guess that they would not be implemented unless someone sponsored it.

[Blama]

Hi Isomorphic,

I agree that your initialSort really makes a better UE if the result is above and will include it in my code

I also try to guarantee that grouping never breaks because the user can configure groupByMaxRecords. This only becomes a problem if there is really to much data to reasonably transfer.

I'd like to emphasize that there is no problem at all (especially not with the 2nd request because grouping breaks), only an enhancement suggestion of defaulting to progressiveLoading:true if a ListGrid is grouped and progressiveLoading is unset/null all the way (ListGrid, operationBinding, DataSource). A set progressiveLoading:false attribute is of course to respect.
In those cases either data is re-requested or (more likely) the amount is below groupByMaxRecords and we therefore request all rows from the JBDC driver and therefore know the rowcount though JBDC via the normal SELECT.

Of course this is very easy set (1 row for the ListGrid-property), but I thought it is a good default optimization where the framework can save an expansive request by default if the developer does not think about it. I did not for many years.

Best regards
Blama