Overview: 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 number 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 an SAP ASE database.
• When SAS/ACCESS can translate operations into the SQL of the DBMS, processing is done in 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.

With the automatic translation abilities of SAS/ACCESS, 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. For information about passing WHERE clauses to the DBMS, see Passing the WHERE Clause to the DBMS.

Typically, the SQL that SAS generates for the DBMS is highly optimized. However, in cases when SQL generation is partial or not optimal, use the information in this chapter to modify the generated SQL that is created by SAS.