SAS/ACCESS takes advantage of DBMS
capabilities by passing certain SQL operations to the DBMS whenever
possible. This can reduce data movement, which can improve performance.
The performance impact can be significant when you access large DBMS
tables and the SQL that is passed to the DBMS subsets the table to
reduce the amount of rows.
SAS/ACCESS sends operations to the DBMS
for processing in these situations.
-
When you use the
SQL pass-through
facility, you submit DBMS-specific
SQL statements that are sent directly to the DBMS for execution. For
example, when you submit Transact-SQL statements to be passed to a
Sybase database.
-
When
SAS/ACCESS can translate the
operations into the SQL of the DBMS. When you use the
SAS/ACCESS
LIBNAME statement, you submit SAS
statements that
SAS/ACCESS can often translate into the SQL of the
DBMS and then pass to the DBMS for processing.
By using the automatic
translation abilities, you can often achieve the performance benefits
of the SQL pass-through facility without needing to write DBMS-specific
SQL code. The following sections describe the SAS SQL operations that
SAS/ACCESS can pass to the DBMS for processing. See
Optimizing the Passing of WHERE Clauses to the DBMS for information
about passing WHERE clauses to the DBMS.
Note: Certain conditions prevent
operations from being passed to the DBMS. For example, when you use
an INTO clause or any data set option, operations are processed in
SAS instead of being passed to the DBMS. Re-merges, union joins, and
truncated comparisons also prevent operations from being passed to
the DBMS.Also, when you join tables across multiple tables, implicit
pass-through uses the first connection. Consequently, LIBNAME options
from subsequent connections are ignored.
You can use the SASTRACE= system option to determine whether SAS processes an operation or whether
pass-through passes it to the DBMS for processing.