33
CHAPTER
5
Optimizing Your SQL Usage
Overview of Optimizing Your SQL Usage
33
Passing Functions to the DBMS Using PROC SQL
34
Passing Joins to the DBMS
34
When Passing Joins to the DBMS Will Fail
35
Passing DISTINCT and UNION Processing to the DBMS
37
Optimizing the Passing of WHERE Clauses to the DBMS 37
Passing Functions to the DBMS Using WHERE Clauses
38
Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options
38
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 are
accessing 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 operations use the Pass-Through Facility. When you use the 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 and PROC SQL, 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 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” on page 37 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.
You can use the SASTRACE= system option to determine whether an operation is
processed by SAS or is passed to the DBMS for processing.
To prevent operations from being passed to the DBMS, use the LIBNAME option
DIRECT_SQL=.
