Testing and Evaluating Performance

Writing Timing Information for Each Statement

The PROC SQL option STIMER | NOSTIMER specifies whether PROC SQL writes timing information for each statement to the SAS log, instead of writing a cumulative value for the entire procedure. NOSTIMER is the default.
In order to use the STIMER option in PROC SQL, the SAS system option STIMER (the default) must also be in effect. Some host operating environments require that you specify the SAS system option STIMER when you invoke SAS. The STIMER system option controls the printing of performance statistics in the SAS log. If you use the system option alone, the results will contain timing information for the entire procedure, not on a statement-by-statement basis.
You can use the OPTIONS procedure to list the current settings of SAS system options. To find out if the SAS system STIMER option is enabled on your operating environment, submit the following program: 
proc options option=stimer value;
run;
Table 8.5 SAS Log 
Option Value Information For SAS Option STIMER 
    Option Value: STIMER 
    Option Scope: SAS Session 
    How option value set: Shipped Default
Note: PROC OPTIONS produces additional information that is specific to the operating environment under which you are running SAS. For more information about this and for descriptions of host-specific options, see the SAS documentation for your operating environment.

Example

Both of the queries in the following PROC SQL step list the name, address, city, state, and ZIP code of customers listed in the Sasuser.FrequentFlyers table. However, the second query lists only this information for customers who have earned more than 7000 points and used less than 3000 points.
When the PROC SQL statement is submitted without the STIMER option, timing information for both queries is written to the SAS log as a cumulative value for the entire procedure. 
proc sql;        
   select name, address, city, state, zipcode 
      from sasuser.frequentflyers; 
   select name, address, city, state, zipcode 
      from sasuser.frequentflyers   
      where pointsearned gt 7000 and pointsused lt 3000;
quit;
Note: Timing information for a PROC SQL step is not written to the SAS log until a QUIT statement is submitted or another PROC or DATA step is started.
Table 8.6 SAS Log 
28   proc sql;
29   select name, address, city, state, zipcode
       from sasuser.frequentflyers;
30   select name, address, city, state, zipcode
       from sasuser.frequentflyers
31     where pointsearned gt 7000 and pointsused lt 3000;
32   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time 0.34 seconds 
      cpu time 0.30 seconds
When the PROC SQL statement is submitted with the STIMER option, timing information is written to the SAS log for each SELECT statement. 
proc sql stimer;        
   select name, address, city, state, zipcode 
      from sasuser.frequentflyers;  
   select name, address, city, state, zipcode 
      from sasuser.frequentflyers  
      where pointsearned gt 7000 and pointsused lt 3000;
quit;
Table 8.7 SAS Log 
33   proc sql stimer;
NOTE: SQL Statement used (Total process time):  
      real time           0.00 seconds  
      cpu time            0.00 seconds
       
34 select name, address, city, state, zipcode 
     from sasuser.frequentflyers;
NOTE: SQL Statement used (Total process time):
      real time 0.22 seconds 
      cpu time 0.17 seconds

35 select name, address, city, state, zipcode
     from sasuser.frequentflyers
36   where pointsearned gt 7000 and pointsused lt 3000;
NOTE: SQL Statement used (Total process time):  
      real time 0.25 seconds
      cpu time 0.08 seconds

37 quit;
NOTE: PROCEDURE SQL used (Total process time): 
      real time 0.29 seconds  
      cpu time 0.03 seconds
Note: When the STIMER option is used in PROC SQL, the exact wording of the Notes that are written to the SAS log might vary for different versions of SAS.
Note: The STIMER option in PROC SQL is useful when an operation can be accomplished in more than one way and you are benchmarking each technique. Although factors such as code readability and maintenance come into consideration, you might also want to know which PROC SQL step runs the fastest.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.12.50