How to use implicit passthrough in PROC SQL?
Altair EmployeeImplicit passthrough is the default PROC SQL mode in Altair SLC. The Altair SLC (acting as database client) will process the PROC SQL and decide if certain portions of the query can be passed to the database to be processed more efficiently e.g
libname mylib odbc ..connection-options..;
proc sql;
select * from mylib.mytable where col1 > 10;
In this query there are 2 options:
(a) all the data can be returned to Altair SLC from the database as a "SELECT * FROM MYTABLE" and the where clause can then be applied by proc sql, or
(b) the where clause can be passed to the database "under the hood". Therefore, proc sql generates a database specific query "SELECT * from MYTABLE where col1 > 10". This can be more efficient than returning the whole table.
Implicit passthrough tries to hand off as much processing (including joins, aggregations etc) as it can to the database to limit the amount of movement of data to and from the database.
If the noipassthru option is specified on the PROC SQL statement, e.g.
PROC SQL noipassthru;
then the where clause will be processed by the Altair SLC client and implicit passthrough will not be attempted. This is sometimes a useful diagnostic check to see if there is an issue with the generated SQL being passed through to the database.
Implicit passthrough allows the Altair SLC query planner to try and make the best decisions about what actually gets executed on the database.