Improve TotalCount's autogenerated Count(*) query performance avoiding redundant joins.

[PabloAlarcon]

Product

Hot Chocolate

Is your feature request related to a problem?

Hello team, keep up the good work.

I've recently discovered this scenario that is having a big impact into several high importance queries in our application ( up to 4-5 seconds total impact for a costly but frequent sorted listing query :( ) :

• Selecting TotalCount in a Paginated query automatically creates a Count(*) query for the base query, applying any FIltering as expected 🟢

• HOWEVER, if the paginated query included Sorting and the Sorting parameters required joining other tables, e.g CostlySortingTableA, the Count(*) generated query keeps joins to these redundant tables. 🔴

Sample:

• Selecting Col1 and Col2 and ordering by a costly expression requiring other table.

-Pseudo query sample

• Pagination query 🟢

SELECT t.Col1,t.Col2
FROM Table1 t
INNER JOIN CostlySortingTableA csta
ORDER BY SUM( csta.ColX ).

• Count query 🔴

SELECT count(*)
FROM Table1 t
INNER JOIN CostlySortingTableA csta

Real example:

• Sorting introduces this very costly join to vw_ReportedDealMetrics_Unified view in the HC autogenerated Count(*) query:

Execution time: 4,440ms

• Compared with the autogenerated Count(*) query where we're not sorting by a parameter that depends on vw_ReportedDealMetrics_Unified view

Execution time: 332ms - 13X+ faster.

The solution you'd like

Count query should prune/avoid the redundant joins that are needed only for sorting.

In the example above

• Expected Count query

SELECT count(*)
FROM Table1 t
INNER JOIN CostlySortingTableA csta

Thoughts:

• I can see the complication with the current Pagination API, i.e

public static ValueTask<Connection> ApplyCursorPaginationAsync(
this IQueryable query,
IResolverContext context,
int? defaultPageSize = null)

where the full composed IQueryable query including Sorting is passed, making it complicated & slow to prune redundant joins.

I guess that something along the lines of:

• Adding an specific method overload to request sorting to be added but not compose it into the IQueryable input
• This allows this method to use the original - non Sorted IQueryable in the Count(*) query while composing the Sorting into the core pagination query.