 |
|
| WHERE-Expression Processing |
Improving Performance of WHERE Processing
Indexing a SAS data set can significantly improve the performance of WHERE processing. An index is an optional file that you can create for SAS data files in order to provide direct access to specific observations.
Processing a WHERE expression without an index requires SAS to sequentially read observations in order to find the ones that match the selection criteria. Without an index, SAS first checks for the sort indicator, which is stored with the data file from a previous SORT procedure or SORTEDBY= data set option. If the sort indicator is validated, SAS takes advantage of it and stops reading the file once it is clear there are no more values that satisfy the WHERE expression. For example, consider a data set that is sorted by Age, without an index. To process the expression where age le 25, SAS stops reading observations after it finds an observation that is greater than 25. Note that while SAS can determine when to stop reading observations, without an index, there is no indication where to begin, so SAS always begins with the first observation, which can require reading a lot of observations.
Having an index enables SAS to determine which observations satisfy the criteria, which is referred to as optimizing the WHERE expression. However, by default, SAS decides whether to use the index or read the entire data set sequentially. For details on how SAS uses an index to process a WHERE expression, see Using an Index for WHERE Processing.
In addition to creating indexes for the data set, here are some guidelines for writing efficient WHERE expressions:
| Guideline | Efficient | Inefficient |
|---|---|---|
| Avoid using the LIKE operator that begins with % or _. |
where country like 'A%INA'; |
where country like '%INA'; |
| Avoid using arithmetic expressions. |
where salary > 48000; |
where salary > 12*4000; |
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.