Skip to main content
Skip table of contents

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

SQL
SELECT
    s.SalesOrderId
FROM
    "mssql_advworks_2019_2.SalesOrderHeader" s
WHERE
    s.SalesOrderId between 43000 and 43999
;;



SQL
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

SQL
SELECT
    s.SalesOrderId
FROM
    "no_pushdown.SalesOrderHeader_ALL" s
WHERE
    s.SalesOrderId between 5000 and 5999
;;



SQL
(s.salesorderid <= '5999') AND (s.salesorderid >= '5000')

SQL
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.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.