10.6. Reviewing CONTENTS Output and System Messages
While no two organizations are alike, it is not surprising to find numerous causes for a program to run at less than peak efficiency. Performance is frequently affected by the specific needs of an organization or its lack of resources. SAS users need to learn as many techniques as possible to correct problems associated with poorly performing programs. Attention should be given to individual program functions, because poor program performance often points to one or more inefficient techniques being used.
Two methods can be used to better understand potential performance issues. The first approach uses PROC CONTENTS output to examine engine/host information and library data sets (tables). The CONTENTS output provides information to determine whether a table is large enough. (The page count in the following output to show the performance improvements offered by an index). The general rule that the SQL processor adheres to is when a table is relatively small (usually fewer than three pages) there is no real advantage in using an index. In fact, using an index with a small table can actually degrade performance levels because in these situations sequential processing would be just as fast as using an index.
Results
The CONTENTS Procedure Data Set Name WORK.INVENTORY |
Observations 7
Member Type DATA
The second approach uses PROC SQL to access the dictionary tables, TABLES and COLUMNS, to determine whether a table is large enough to take advantage of the performance improvements offered by an index. See the output below.
SQL Code
PROC SQL;
SELECT MEMNAME, NPAGE
FROM DICTIONARY.TABLES
WHERE LIBNAME='WORK' AND
MEMNAME='INVENTORY';
SELECT VARNUM, NAME, TYPE, LENGTH, FORMAT,
INFORMAT, LABEL
FROM DICTIONARY.COLUMNS
WHERE LIBNAME='WORK' AND
MEMNAME='INVENTORY';
QUIT; The SAS System
|
The table below compares sequential table access with indexed table access. Although performance gains are data dependent, the greatest gains are realized when an index is applied to a small subset of data in a WHERE clause.
Sequential Versus Indexed Table Access
System messages are displayed to provide information that can help tune the indexes associated with any data sets. Setting the MSGLEVEL= system option to “I” allows the SAS System to display vital information (if available) related to the presence of one or more indexes for optimization of WHERE clause processing. With the MSGLEVEL= option turned on, the SAS log shows that the simple index INVENQTY was selected in the optimization of WHERE clause processing.
SAS Log
PROC SQL;
CREATE INDEX INVENQTY ON INVENTORY;
NOTE: Simple index invenqty has been defined.
NOTE: PROCEDURE SQL used:
real time 0.04 seconds
SELECT *
FROM INVENTORY
WHERE invenqty < 3;
INFO: Index invenqty selected for WHERE clause
optimization.
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.65 seconds |