[Solved] Aggregates/Paging causes an additional/unnecessary SELECT, and severe performance issues

[gregtayl]

I have a grid defined similar to the below snippet, with aggregates, paging, and grouping enabled. I have approximately 30 columns worth of numeric data, which when grouped, must display totals in the footer. It may be worth mentioning that we are using the Entity Framework and Linq to Entities (however, this should be irrelevant), and Telerik MVC Extensions 2012.1.214. 

When bound, a total of 3 SELECT statements are executed:

• The first, retrieving a count of all matching records
• The second, retrieving ALL matching records (regardless of page size), along with the corresponding aggregated values
• The third, retrieving the top 10 (page size) matching records along with along with the corresponding aggregated values 

Surely, this second SELECT should never be executed? In my situation we are often paging upwards of 800,000 records - pulling all of these values into memory is causing a severe performance issue.

Am I perhaps missing something trivial, or is this a bug?

@(Html.Telerik().Grid<MyObject>()
     .Columns(columns =>
     {
          columns.Bound(i => i.PropertyOne)
                .Aggregate(aggregates => aggregates.Sum())
                .ClientGroupFooterTemplate("<#= Sum #>");

          columns.Bound(i => i.PropertyTwo)
                .Aggregate(aggregates => aggregates.Sum())
                .ClientGroupFooterTemplate("<#= Sum #>");

          /*-- etc. --*/
     })
    .DataBinding(dataBinding => dataBinding.Ajax().Select("MySelect", "MyController"))
    .Groupable(groupable => groupable.Enabled(true)))

[Rosen]

Hi Greg,

We were able to observe the behavior you have described. However, it seems that the additional statement is actually generated by the Entity Framework Query Provider. As you may know the generated LINQ query is almost identical regardless of the QueryProvider used. Then the QueryProvider will translate this expression tree to the appropriate format (in this case SQL) and execute it. In this particular case the EF Query provider will use three statements as opposite to the LinqToSQL provider for example, which will use just two for the same expression tree. Thus, unfortunately, there is little we can do in this case.

All the best,
Rosen
the Telerik team

[gregtayl]

Hi Rosen,

Thanks for the quick reply.

Is this something that the Telerik team plans to investigate/resolve? I understand that how the Entity Framework resolves a LINQ statement to SQL is ultimately outside of Telerik's control, however, perhaps the method in which aggregation is applied can be modified to assist EF in generating a more performant query. Or perhaps providing users with the ability to override/customize how aggregation is applied would be a simpler feat.

Given the fact that EF is a fairly popular ORM I would expect that a substantial number of Telerik's customers will eventually run into this situation/limitation.

Can you suggest any alternative approaches to achieve group summary rows?

Thanks,
Greg

[Rosen]

Hi Greg,

I'm afraid that we are not aware of different expression which will be translated to more efficient SQL by the EF and achieve the same functionality. However, if you are using grid AJAX binding, you may take a look at the attached sample which demonstrates a very basic implementation of custom binding with aggregates. You may use it as a starting point to handle the binding of the grid yourself.

Regards,
Rosen
the Telerik team

[gregtayl]

Hi Rosen,

As expected, the SQL generated is different than that of the Entity Framework, however, after profiling the Telerik source code and examples, it's apparent that the above described additional selection behaviour actually DOES occur using LinqToSQL.

Navigate to the "Grouping/Aggregates-> Ajax Aggregates" demo (http://localhost/Telerik.Web.Mvc.Examples/grid/aggregatesajax), using the paging control, navigate to the second page (for some reason this behaviour does not occur on the first page?).

The following SQL statements are generated:

1. Count of ALL matching records (1 record)
2. Aggregation summary for ALL matching records (1 record)
3. All records (ignoring page size) (77 records)
4. Aggregation summaries for each group level displayed in the current page (7 records)
5. All records in the 1st group
6. All records in the 2nd group
7. All records in the 3rd group
8. All records in the 4th group
9. All records in the 5th group
10. All records in the 6th group
11. All records in the 7th group

Once again, an additional SQL statement (#3) is generated returning ALL records regardless of page size. In this particular case, only 77 items are being displayed by the grid so the performance hit is negligible - however in the case of a large dataset, I would expect that this is not the case.

As a side note, many publicly available articles (including a few from Microsoft) suggest that LinqToSql is soon to be (or already is) deprecated. Given that the Entity Framework is Microsoft's flagship ORM, I would strongly urge the Telerik team to optimize/target the control-suite towards it instead.

-Greg

[Rosen]

Hello Greg,

After closer examination, we are able to tweak a bit the expression tree generation and loose the extra SQL query. I have attached an internal build which contains the change. Please give it a spin and see if there is a change at your end.

All the best,
Rosen
the Telerik team