Optimizing Your SQL Usage

Overview of Optimizing Your SQL Usage

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 the following 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: There are certain conditions that 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.

Additionally, it is important to note that 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 system option to determine whether an operation is processed by SAS or is passed to the DBMS for processing. [cautionend]

To prevent operations from being passed to the DBMS, use the LIBNAME option DIRECT_SQL=.

Top of Page