29
CHAPTER
4
Performance Considerations
Increasing Throughput of the SAS Server
29
Limiting Retrieval
29
Repeatedly Accessing Data
30
Sorting DBMS Data
31
Increasing Throughput of the SAS Server
When you invoke SAS as a server that responds to multiple clients, you can use the
DBSRVTP= system option to improve the performance of the clients. The DBSRVTP=
option tells the SAS server whether to put a hold (or block) on the originating client
while making performance-critical calls to the database. By holding or blocking the
originating client, the SAS/ACCESS server remains available for other clients; they do
not have to wait for the originating client to complete its call to the database.
Limiting Retrieval
Limiting the number of rows that are returned to SAS from the DBMS is an
extremely important performance consideration, because the less data that is requested
by the SAS job, the faster the job runs.
Where possible, specify selection criteria in order to limit the number of rows that
the DBMS returns to SAS. Use the SAS WHERE clause to retrieve a subset of the
DBMS data.
Likewise, select only the DBMS columns that your program needs. Selecting
unnecessary columns slows your job. Just as with a SAS data set you can use the
DROP= and KEEP= data set options to prevent retrieving unneeded columns from your
DBMS table.
In this example, the KEEP= data set option causes the SAS/ACCESS engine to select
only the SALARY and DEPT columns when it reads the MYDBLIB.EMPLOYEES table.
libname mydblib db2 user=testid password=testpass database=testdb;
proc sql;
select *
from mydblib.employees(keep=salary dept)
where dept=’ACC024’;
quit;
The generated SQL that is processed by the DBMS will be similar to the following:
