Program Testing and Performance 1 Testing and Performance

Program Testing and Performance 1

Testing and Performance Options Option Effect EXEC|NOEXEC controls whether or not submitted SQL statements are executed. NOSTIMER|STIMER reports performance statistics in the SAS log for each SQL statement. NOERRORSTOP| ERRORSTOP makes PROC SQL enter syntax-check mode after an error occurs; usually used in batch and non-interactive submissions. 2

Testing and Performance Options Display the columns that are retrieved when you use SELECT * in a query, and display any macro variable resolutions, but do not execute the query. %let big=100000; proc sql feedback noexec; select * from orion. Employee_payroll where salary>&big; quit; 3

Performance Benchmarking System performance issues are usually caused by bottlenecks in one of three major resources: CPU Memory Input/Output (I/O) An overload of any one of these resources can significantly increase the elapsed time required to execute your program. 4

Performance Benchmarking You can use the STIMER or FULLSTIMER options to gather information on how your SAS programs use CPU, memory, and I/O. OPTIONS STIMER; OPTIONS FULLSTIMER; The STIMER SAS system option causes SAS to print performance statistics in the SAS log for each DATA or PROC step executed. The FULLSTIMER option provides greater detail in performance reporting. Not all statistics are available on all operating systems, so the results might differ between operating environments. 5

Performance Benchmarking The STIMER option can also be specified as a PROC SQL option: proc sql stimer; When used in conjunction with the STIMER or FULLSTIMER SAS system option, the PROC SQL STIMER option provides CPU, memory, and I/O performance information for each individual statement executed by PROC SQL during a single invocation. This enables a more granular analysis of resource utilization. 6

Testing and Performance Options Example: Capture performance statistics for a complex query. options fullstimer; proc sql stimer; select distinct catx(' ', scan(Employee_Name, 2, ', '), scan(Employee_Name, 1, ', ')) format=$25. as Manager, City from orion. Order_Fact as of, orion. Product_Dim as pd, orion. Employee_Organization as eo, orion. Employee_Addresses as ea where of. Product_ID=pd. Product_ID and of. Employee_ID=eo. Employee_ID and ea. Employee_ID=eo. Manager_ID and Product_Name contains 'Expedition Zero' and year(Order_Date)=2003 and eo. Employee_ID ne 9999 ; quit; 7

Benchmarking Guidelines Elapsed time is affected by concurrent tasks and should not normally be used for benchmarking. Always benchmark your programs in separate SAS sessions. If benchmarking is done on different methods within a single SAS session, statistics for the second method can be misleading. SAS might retain modules loaded into memory or the operating system might cache data read from a disk that was used in prior steps. continued. . . 8

Benchmarking Guidelines Run each program multiple times and average the performance statistics. Use realistic data for tests. Method A could be much more efficient than Method B when applied to small tables, but much less efficient on large tables. 9

SAS had been running about an hour. options fullstimer; proc sql stimer; create table counts as select app_id, count(app_id) from kag. train group by app_id ; quit;

Shutdown SAS, restart, immediately run