/* Chapter 13: Performance Tuning */

/* Jump to: Chapter 12    Table of Contents    Chapter 14 */

/*

• 13.1: Resource Example: The Effect of an Index
• 13.2: Code Example: The Advantage of Equijoins

*/

/* 13.1: Resource Example: The Effect of an Index */

/* Jump to: Next Section */

DATA myRandoms;                                                                 
DO sernum = 1 TO 2E7;                                                           
   myRandom = ROUND(1E6*RANUNI(1) );                                            
   OUTPUT;                                                                      
   END;                                                                         
RUN;                                                                            
                                                                                
PROC SQL STIMER;                                                                
SELECT       COUNT(*)                                                           
INTO         : count654321                                                      
FROM         myRandoms                                                          
WHERE        myRandom = 654321                                                  
;                                                                               
%PUT &count654321 rows counted.;                                                
QUIT;                                                                           
                                                                                
PROC SQL STIMER;                                                                
CREATE INDEX myRandom ON myRandoms;                                             
QUIT;                                                                           

/* Jump to: Top of Section     Top of Chapter */

/* 13.2: Code Example: The Advantage of Equijoins */

/* Jump to: Next Section */

DATA roster;                                                                    
DO i = 1 TO 3e4; DROP i;                                                        
   name = i;                                                                    
   phone = i + 0.1;                                                             
   email = i + 0.2;                                                             
   OUTPUT;                                                                      
   IF RANUNI(111)>0.8  THEN name = name + 0.01;                                 
   IF RANUNI(111)>0.8  THEN OUTPUT;                                             
   END;                                                                         
RUN;                                                                            
                                                                                
PROC SQL STIMER;                                                                
CREATE TABLE same_phone AS                                                      
SELECT       DISTINCT roster.name,                                              
                      copy.name AS diff_name                                    
FROM         roster JOIN roster AS copy                                         
ON           roster.phone=copy.phone                                            
WHERE        roster.name LT copy.name                                           
;                                                                               
QUIT;                                                                           
                                                                                
PROC SQL STIMER;                                                                
CREATE TABLE slow AS                                                            
SELECT       DISTINCT roster.name,                                              
                      copy.name AS diff_name                                    
FROM         roster JOIN roster AS copy                                         
ON           roster.phone=copy.phone OR                                         
             roster.email=copy.email                                            
WHERE        roster.name LT copy.name;                                          
QUIT;                                                                           
                                                                                
PROC SQL STIMER;                                                                
CREATE TABLE fast AS                                                            
SELECT       roster.name,                                                       
             copy.name AS diff_name                                             
FROM         roster JOIN roster AS copy                                         
ON           roster.phone=copy.phone                                            
WHERE        roster.name NE copy.name                                           
UNION                                                                           
SELECT       roster.name,                                                       
             copy.name AS diff_name                                             
FROM         roster JOIN roster AS copy                                         
ON           roster.email=copy.email                                            
WHERE        roster.name NE copy.name;                                          
QUIT;                                                                           

/* Jump to: Top of Section     Top of Chapter */