SAS Program Testing and Performance Options
1
P
r
o
g
r
a
m
T
e
s
t
i
n
g
a
n
d
P
e
r
f
o
r
m
a
n
c
e
Testing and Performance Options
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.
3
%let
big=100000;
proc
sql
feedback
noexec
;
select
*
from
orion.Employee_payroll
where
salary>&big;
quit
;
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.
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
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
The STIMER option can also be specified as a
PROC SQL option:
proc sql stimer;
Testing and Performance Options
Example: Capture performance statistics for a complex query.
7
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
99999999
;
quit
;
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.
8
continued...
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
options
fullstimer
;
proc
sql
stimer
;
create
table
counts
as
select
app_id,count(app_id)
from
kag.train
group
by
app_id
;
quit
;
SAS had been running about an hour.
Shutdown SAS, restart, immediately run
Explore the various testing and performance options available in SAS programming, including controlling SQL statement execution, capturing performance statistics, benchmarking system performance, and utilizing STIMER and FULLSTIMER options for detailed resource analysis. Learn how to optimize program efficiency and identify potential bottlenecks affecting CPU, memory, and I/O performance.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Testing and Performance Options Option Effect controls whether or not submitted SQL statements are executed. EXEC|NOEXEC reports performance statistics in the SAS log for each SQL statement. NOSTIMER|STIMER 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 proc sql sql feedback noexec; select * from orion.Employee_payroll where salary>&big; quit 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 proc sql sql stimer; select distinct catx(' ',scan(Employee_Name,2 2,','), scan(Employee_Name,1 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 99999999 ; quit quit; 2003 99999999 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. 8 continued...
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 proc sql sql stimer; create table counts as select app_id,count(app_id) from kag.train group by app_id ; quit quit;
