38 Passing Functions to the DBMS Using WHERE Clauses Chapter 5
Whenever possible, SAS/ACCESS passes WHERE clauses to the DBMS, because the
DBMS processes them more efficiently than SAS does. SAS translates the WHERE
clauses into generated SQL code. The performance impact can be particularly
significant when you are accessing large DBMS tables. The following section describes
how and when functions are passed to the DBMS. For information about passing
processing to the DBMS when you are using PROC SQL, see “Overview of Optimizing
Your SQL Usage” on page 33.
Note: If you have NULL values in a DBMS column that is used in a WHERE clause,
be aware that your results might differ depending on whether the WHERE clause is
processed in SAS or is passed to the DBMS for processing. This is because DBMSs tend
to remove NULL values from consideration in a WHERE clause, while SAS does not.
To prevent WHERE clauses from being passed to the DBMS, use the LIBNAME
option DIRECT_SQL= NOWHERE.
Passing Functions to the DBMS Using WHERE Clauses
When you use the SAS/ACCESS LIBNAME statement, SAS/ACCESS translates
several SAS functions in WHERE clauses into DBMS-specific functions so they can be
passed to the DBMS.
In the following SAS code, SAS can translate the FLOOR function into a DBMS
function and pass the WHERE clause to the DBMS.
libname myoralib oracle user=testuser password=testpass;
proc print data=myoralib.personnel;
where floor(hourlywage)+floor(tips)<10;
run;
The generated SQL that is processed by the DBMS would be similar to
SELECT "HOURLYWAGE", "TIPS" FROM PERSONNEL
WHERE ((FLOOR("HOURLYWAGE") + FLOOR("TIPS")) < 10)
If the WHERE clause contains a function that SAS cannot translate into a DBMS
function, SAS retrieves all the rows from the DBMS and then applies the WHERE
clause.
The functions that are passed are different for each DBMS. See the documentation
for your SAS/ACCESS interface to determine which functions it translates.
Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options
When you code a join operation in SAS, and the join cannot be passed directly to a
DBMS for processing, the join will be performed by SAS. Normally this processing will
involve individual queries to each data source that belonged to the join, and the join
being performed internally by SAS. When you join a large DBMS table and a small SAS
data set or DBMS table, using the DBKEY= , DBINDEX=, and
MULTI_DATASRC_OPT= options might enhance performance. These options enable
you to retrieve a subset of the DBMS data into SAS for the join.
When MULTI_DATASRC-OPT=IN_CLAUSE is specified for DBMS data sources in a
PROC SQL join operation, the procedure will retrieve the unique values of the join
column from the smaller table to construct an IN clause. This IN clause will be used
when SAS is retrieving the data from the larger DBMS table. The join is performed in
SAS. If a SAS dataset is used, no matter now large, it is always in the IN_CLAUSE.
