Optimizing Your SQL Usage

Optimizing the Passing of WHERE Clauses to the DBMS

Follow these 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 for details about these 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 of 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, 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;

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 then applies the WHERE clause.

The functions that are passed are different for each DBMS. Select your DBMS below to view a list of the functions that your SAS/ACCESS interface translates.

Top of Page