Optimizing Your SQL Usage Optimizing the Passing of WHERE Clauses to the DBMS 37
DIRECT_SQL=NOGENSQL
PROC SQL does not attempt to pass SQL join queries to the DBMS. Other
SQL statements can be passed down, however. If the
MULTI_DATASRC_OPT= option is in effect, the generated SQL can be
passed.
DIRECT_SQL=NOMULTOUTJOINS
PROC SQL does not attempt to pass any multiple outer joins to the DBMS
for direct processing. Other SQL statements can be passed, however,
including portions of a multiple outer join.
Passing DISTINCT and UNION Processing to the DBMS
When you use the SAS/ACCESS LIBNAME statement to access DBMS data, the
DISTINCT and UNION operators are processed in the DBMS rather than in SAS. For
example, when PROC SQL detects a DISTINCT operator, it passes the operator to the
DBMS to check for duplicate rows. The DBMS then returns only the unique rows to
SAS.
In the following example, the Oracle table CUSTBASE is queried for unique values
in the STATE column.
libname myoralib oracle user=testuser password=testpass;
proc sql;
select distinct state from myoralib.custbase;
quit;
The DISTINCT operator is passed to Oracle, generating the following Oracle code:
select distinct custbase."STATE" from CUSTBASE
Oracle then passes the results from this query back to SAS.
Optimizing the Passing of WHERE Clauses to the DBMS
Use the following general guidelines for writing efficient WHERE clauses:
Avoid the NOT operator if you can use an equivalent form.
Inefficient:
where zipcode not>8000
Efficient: where zipcode<=8000
Avoid the >= and <= operators if you can use the BETWEEN predicate.
Inefficient:
where ZIPCODE>=70000 and ZIPCODE<=80000
Efficient: where ZIPCODE between 70000 and 80000
Avoid LIKE predicates that begin with % or _ .
Inefficient:
where COUNTRY like ’%INA’
Efficient: where COUNTRY like ’A%INA’
Avoid arithmetic expressions in a predicate.
Inefficient:
where SALARY>12*4000.00
Efficient: where SALARY>48000.00
Use DBKEY=, DBINDEX=, and MULTI_DATASRC_OPT= when appropriate. See
“Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options” on page
38 for details about these options.
