Identifying Conditions That Can Be Optimized

In addition to containing key variables, WHERE conditions must meet other requirements in order to be candidates for optimization. SAS considers using an index only for WHERE conditions that contain certain operators and functions. Therefore, the next step for SAS is to consider the operators and functions in the conditions that contain key variables.

Requirements for Optimizing a Single WHERE Condition

SAS considers using an index for a WHERE condition that contains any of the following operators and functions:
Note: For all of the following examples, assume that the data set has simple indexes on the variables Quarter, Date_ID, and Region.
Operator
Example
comparison operators
 
where quarter = '1998Q1';
where date_id < '03JUL2000'd;
where quarter in ('1998Q2','1998Q3'),
comparison operators with NOT 
where quarter ne '1999Q1';
where quarter not in ('1999Q1','1999Q4'),
comparison operators with the colon modifier
You can add a colon modifier (:) to any comparison operator to compare only a specified prefix of a character string.
The colon modifier cannot be used with PROC SQL; use the LIKE operator instead. 
where quarter =: '1998';
CONTAINS operator
 
where quarter contains 'Q4';
fully bounded range conditions that specify both an upper and lower limit, which includes the BETWEEN-AND operator 
where '01Jan1999'd < date_id
      < '31Dec1999'd;
where date_id between '01Jan1999'd
              and '31Dec1999'd
pattern-matching operator LIKE 
where quarter like '%Q%';
IS NULL or IS MISSING operator 
where quarter is null;
where quarter is missing;
Function
Example
TRIM function
 
where trim(region) = 'Queensland';
SUBSTR function in the form of
WHERE SUBSTR
(variable,position,length)='string';
with these conditions:
  • position = 1
  • length is less than or equal to the length of variable
  • length is equal to the length of the string 
where substr(quarter,1,4) = '1998';
CAUTION:
Most but not all of the requirements listed above also apply to compound optimization. Requirements for compound optimization are covered later in this chapter.

WHERE Conditions That Cannot Be Optimized

SAS does not use an index to process a WHERE condition that contains any of the elements listed below.
Note: For all of the following examples, assume that the data set has simple indexes on the variables Date_ID, Quarter, and Quantity.
Element in WHERE Condition
Example
any function other than TRIM or SUBSTR 
where weekday(date_id)=2;
a SUBSTR function that searches a string beginning at any position after the first 
where substr(quarter,6,1)='1';
the sounds-like operator (=*) 
where quarter=*'1900Q0';
arithmetic operators
 
where quantity=quantity+1;
a variable-to-variable condition 
where quantity gt threshold;

Requirements for Compound Optimization

Most of the same operators that are acceptable for optimizing a single condition are also acceptable for compound optimization. However, compound optimization has special requirements for the operators that appear in the WHERE expression:
  • The WHERE conditions must be connected by using either the AND operator or, if all conditions refer to the same variable, the OR operator.
  • At least one of the WHERE conditions that contain a key variable must contain the EQ or IN operator.

Example: Compound Optimization

Suppose your program contains the following WHERE statement, which selects all people whose name is John Smith. The WHERE statement contains two conditions. Each condition references a different variable: 
where lastname eq 'Smith' and 
      frstname eq 'John';
Suppose Lastname is the first key variable and Frstname is the second key variable in a compound index. This WHERE statement meets all requirements for compound optimization:
  • The WHERE expression references at least the first two key variables in one composite index.
  • The two WHERE conditions are connected by the AND operator.
  • At least one of the conditions contains the EQ operator.
If the two conditions in the WHERE statement are reversed, as shown below, the statement still meets all requirements for compound optimization. The order in which the key variables appear does not matter. 
where frstname eq 'John' and 
      lastname eq 'Smith';
Now suppose that the conditions in the WHERE statement are joined by the operator OR instead of AND: 
where frstname eq 'John' or
      lastname eq 'Smith';
These conditions cannot be optimized because they are joined by OR but they do not reference the same variable.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.117.93.0