Optimizing the Passing of WHERE Clauses to the DBMS

General Guidelines for WHERE Clauses

Follow the general guidelines in this table for writing efficient WHERE clauses.

Efficient WHERE Clause Guidelines

Guideline	Inefficient	Efficient
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. For details about these options, see Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options.

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: Optimizing Your SQL Usage.

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, but SAS does not.

To prevent WHERE clauses from being passed to the DBMS, use the DIRECT_SQL= NOWHERE LIBNAME option.

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 that they can be passed to the DBMS.

In this 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;

Generated SQL that the DBMS processes would be similar to this code.

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 rows from the DBMS and applies the WHERE clause.

The functions that are passed are different for each DBMS. Refer to the documentation for your SAS/ACCESS interface in the list below to determine which functions it translates.