Aggregation Pushdown

In this example, the query is performing a GROUP BY and a SUM:

SELECT
    "ProductID"
    ,sum ("UnitPrice")
FROM
    "mssql_advworks_2019.AdventureWorks2019.Sales.SalesOrderDetail"
GROUP BY
    "ProductID" LIMIT 500;;

Since MS SQL supports both operations, the entire query is pushed down to the data source. This can be verified by inspecting the query plan.

In the following example, the query is performing a similar operation of GROUP BY, SUM, and ORDER BY, but against a different data source which does not support these operations natively:

SELECT
    "salespersonid"
    ,sum(CAST("totaldue" AS float)) AS "totaldue"
FROM
    "no_pushdown_sum.SalesOrderHeader_All"
GROUP BY
    "salespersonid"
ORDER BY
    "salespersonid" LIMIT 500;;

Upon inspecting the query plan, we can see it is very different:

The detailed query plan for #1 is shown below. Because the data source does not implement GROUP BY nor SUM, the first step is to read all of the data into CData Virtuality:

In step #2, CData Virtuality performs the grouping and aggregation in memory:

In step #3, CData Virtuality implements the LIMIT 500 part of the query: