SAS Code: Simple Macro to Benchmark Data Performance

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.

When troubleshooting data performance problems, IO problems, or simple library connectivity, I use a simple snippet of code to push the data storage to the limits. It is useful because it stresses the data storage component, whether it be a SAS dataset on local file storage or an external database like Oracle, and it provides useful performance metrics. To make it even better, since this is a SAS Macro it can easily be called multiple times for different libraries by setting the 2 parameters in the %BENCHMARK() macro.

Data Benchmark Macro Code

The following code will take the CLASS dataset from the SASHELP library and continually append a specified number of iterations. Be sure to include the OPTIONS statement to get the vital statistics in the log.

options fullstimer threads=yes; 
%macro benchmark(lib,num); 
  data &amp;lib..test;         
     set
     %do i=1 %to #             
           sashelp.class         
     %end;         
;     
run; 
%mend benchmark; 

%benchmark(work,10000);

Output

This is the output produced when the FULLSTIMER option is set. These statistics were produced on my laptop with a solid state drive.

NOTE: DATA statement used (Total process time):
real time 15.08 seconds
user cpu time 13.55 seconds
system cpu time 1.45 seconds
memory 414561.92k
OS Memory 439844.00k
Timestamp 08/01/2012 11:00:11 PM

How fast can you run this code? Remember to set the macro to loop 10,000 times.

Gather Statistics Over Time with %LOGPARSE

Once a simple (or complex) benchmark task (or even multiple tasks ) has been created like the one above, you can use the log parse macro to capture and store the statistics in your own dataset over time. SAS support provides excellent documentation on obtaining and using the %LOGPARSE macro. That way if a pesky end user constantly complains of poor performance you can provide the analytics to either back their claim or possibly rule out the data storage.