Criteria Pushdown
You are looking at an older version of the documentation. The latest version is found here.
If the data source supports filtering, the filtering criteria are sent to the data source reducing the data returned to Data Virtuality. The simplest example of criteria pushdown is the inclusion of the WHERE
clause in SQL. In cases where the data source does not support WHERE
clauses, Data Virtuality will retrieve the data set and filter the results in memory.
In the examples below, we contrast the query plans for data sources that support and do not support criteria pushdown.
Example 1: Pushdown supported
SELECT
s.SalesOrderId
FROM
"mssql_advworks_2019_2.SalesOrderHeader" s
WHERE
s.SalesOrderId between 43000 and 43999
;;
SELECT g_0.SalesOrderID FROM mssql_advworks_2019_2.SalesOrderHeader AS g_0 WHERE (g_0.SalesOrderID >= 43000) AND (g_0.SalesOrderID <= 43999)
Example 2: Pushdown NOT supported
SELECT
s.SalesOrderId
FROM
"no_pushdown.SalesOrderHeader_ALL" s
WHERE
s.SalesOrderId between 5000 and 5999
;;
(s.salesorderid <= '5999') AND (s.salesorderid >= '5000')
SELECT no_pushdown.SalesOrderHeader_All.salesorderid FROM no_pushdown.SalesOrderHeader_All
The default capabilities of a data source are defined inside the Data Virtuality connectors, but they can also be overridden using the parameters such as SupportsInnerJoins
, SupportsLikeCriteria
, and others described here.