Performance issue about devextreme.aspnet.data

[vconst]

To implement total summary caching you can use the following code in the onBeforeSend handler:

onBeforeSend: (function() {
    var lastFilter; 
    return function(method, ajaxOptions) {
        var data = ajaxOptions.data;
        if(data.totalSummary) {
            var currentFilter = data.filter || null;
            
            if(lastFilter !== undefined && lastFilter === currentFilter) {
                delete data.totalSummary;
            }
            lastFilter = currentFilter;
        }
    };
})()

[AlekseyMartynov]

There are no plans to add such caching capabilities to dxDataGrid in the near future (however, the team is aware of your use case and will track similar inquiries).

Consider another approach. It's possible to implement a custom caching store in JavaScript (see What Are Stores and CustomStore API reference) which will sit between the grid and the store provided by this library. Inside it, you'll be able to code any logic of caching and modifying web requests and responses, instead of relying upon undocumented behaviors.

[AlekseyMartynov]

This library implements a RESTful stateless protocol. If the grid asks for totals then we calculate them. Summary caching is an interesting idea, I'll share it with the team who works on dxDataGrid.

Technically, you may strip summary-related parameters from a request by handling onBeforeSend, based on some custom state. After a quick look, I expect that dxDataGrid keeps existing summaries if they are absent from the response. However, haven't checked it.

If I'm not mistaken, you're using EF Core which currently doesn't translate GroupBy to SQL. If it did then repeated aggregates would be cached on the database level and wouldn't present a problem.

If it's the case, I suggest that you leave your feedback in dotnet/efcore#2341. It might help prioritize GroupBy support.

/cc @vconst

[AlekseyMartynov]

P.S. There is another possibility for .NET Core - to issue multiple aggregate queries, one per total summary item instead of a single GroupBy. It could be a good trade-off.

[twenzel]

Actually we're using a custom made Linq Provider based on Remotion.Linq.SqlBackend and we already added support for the "RemoteGrouping" feature.

So thanks for the hint stripping parameters in the onBeforeSend event. Maybe this can be an extension of the cliend side js library.

[AlekseyMartynov]

Interesting. With GroupBy being translated to SQL and database query caching in action, can you say that querying for totals on each request has a noticeable impact on performance?

[twenzel]

With our demo database and displaying "all" records (not filtered to any criteria) the querying for totals takes up to 2 seconds (table contains ~2million records). But this is not representativ because the data set is much smaller when using in production.

[twenzel]

Thanks @vconst. Is this possible to implement this as default behaviour of the grid or provide an "extension" method for this?

I've extended the sample to remove the requireTotalCount also.
Unfortunately due this "hack", the grid creates a seconds request with "IsCountQuery" to retrieve the count value. Is there an option that the grid remembers the total count value the same way it remembers the total summaries? Or is there a way to inject a value after the request returns from the server?