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
Variables 5
Engine V9
Indexes 0
Created Friday, August 20, 2004
Observation Length 20
Last Modified Friday, August 20, 2004
Deleted Observations 0
Protection
Compressed NO
Data Set Type
Sorted NO
Label
Data Representation Windows_32
Encoding Wlatin1 Western (Windows)
Engine/Host Dependent
Information
Data Set Page Size 4096
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 202
Obs in First Data Page 7
Number of Data Set Repairs 0
File Name D:SAS Version 9.1SAS
Temporary Files
\_TD1632inventory
.SAS7bdat
Release Created 9.0101M0
Host Created XP_HOME
-----Alphabetic List of Variables and
Attributes-----
# Variable Type Len Pos Format
Informat Label
__________________________________________________
_____________________________________________
4 invencst Num 6 18 DOLLAR10.2
Inventory Cost
2 invenqty Num 3 15
Inventory Quantity
5 manunum Num 3 24
Manufacturer Number
3 orddate Num 4 8 MMDDYY10.
MMDDYY10. Date Inventory Last Ordered
1 prodnum Num 3 12
Product Number
-----Alphabetic List of Indexes and
Attributes-----
Current # of
Update Update Unique
# Index Centiles Percent
Values Variables
__________________________________________________
____________________________________________
1 invenqty 5 0 5
---
2.00036621095932
---
-8.7692233015159E304
---
-1.0318151782291E270
---
5.00073295836049
---
-8.7392210587264E304
---
-1.4195819273297E135
---
20.0029327871163
---
-8.7220769199897E304 |
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 Number Member Name of Pages __________________________________________ INVENTORY 1 The SAS System Column Number Column Column Column Column Column in Table Column Type Length Format Informat Label __________________________________________________ ________________________________________ 1 prodnum num 3 Product Number 2 invenqty num 3 Inventory Quantity 3 orddate num 4 MMDDYY10. MMDDYY10. Date Inventory Last Ordered 4 invencst num 6 DOLLAR10 .2 Inventory Cost 5 manunum num 3 Manufacturer Number |
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.
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 |