DATA Step, Macro, Functions and more

Macro sql feels slow - please run this code and tell me your results!

Reply
Occasional Contributor MBI
Occasional Contributor
Posts: 19

Macro sql feels slow - please run this code and tell me your results!

Hi everyone,

I'm having what I perceive as slowness issues in my macro sql code and want to determine if it's just my computer. The code below cycles through creating and deleting a table 1000 times. It takes my computer 18 seconds to run. If anyone has a spare minute, can you let me know how long this takes to run on your pc and what kind of hardware you're using?

Thanks,

-MBI


%macro timetest();
%do i = 1 %to 1000;
proc sql noprint;
CREATE TABLE tabletest (test int);
DROP TABLE tabletest;
run;
%end;
%mend timetest;
%timetest()
Trusted Advisor
Posts: 2,115

Re: Macro sql feels slow - please run this code and tell me your results!

About 6.6 seconds of cpu time. Run twice.

batch SAS 9.2, Phase 2. Windows XP SP 2, Lenovo T61p laptop.
Valued Guide
Posts: 2,177

Re: Macro sql feels slow - please run this code and tell me your results!

1 min 17.5secs on a busy 32bit win-XP machine with 4gb memory, running SAS9.1.3-sp4 Display Manager
N/A
Posts: 0

Re: Macro sql feels slow - please run this code and tell me your results!

10 secs 1.86GHz Processor, 1.5GB RAM, Windows XP.

~ Sukanya E
Contributor
Posts: 30

Re: Macro sql feels slow - please run this code and tell me your results!

0.01 seconds using EG4.1 on citrix
PROC Star
Posts: 1,759

Re: Macro sql feels slow - please run this code and tell me your results!

Posted in reply to DerekAdams
3.4 s elapse on
SAS (r) 9.1 (TS1M3) DMS
XP_PRO SP2
E8500 @ 3.16 GHz

Peter, do something about your PC! Smiley Happy
Valued Guide
Posts: 2,177

Re: Macro sql feels slow - please run this code and tell me your results!

Being teased a little about my system performance, I decided to look a little closer.
I'm using a single processor machine, which might affect results.
I was testing the run under DM, where calculating total cpu time is inconvenient across 100 steps, let alone 1000!
Under batch SAS9.1 the log shows the totalled time at a more reasonable pace[pre] real time 22.46 seconds
user cpu time 1.49 seconds
system cpu time 2.82 seconds[/pre]
However, under DM there are extra overheads (like EG but different).
To simplify the calculation of total cpu for 1000 creates/deletes, I tested this variation
%macro timetst() ;
%do i = 1 %to 1000 ;
CREATE TABLE tabletest (test int) ;
DROP TABLE tabletest ;
%end ;
%mend timetst ;

proc sql noprint;
%timetst() ;
quit;

This provides times for the single SQL step:[pre]NOTE: PROCEDURE SQL used (Total process time):
real time 18.46 seconds
user cpu time 0.92 seconds
system cpu time 2.10 seconds
Memory 60k [/pre]
Running the code in batch provided these step times[pre]NOTE: PROCEDURE SQL used (Total process time):
real time 15.71 seconds
user cpu time 0.85 seconds
system cpu time 2.42 seconds
Memory 60k[/pre]
There is no way my elapse time would be below 10 secs let alone under 4 secs!

Perhaps I should not load up my machine with so many activities;-)

PeterC
Contributor
Posts: 30

Re: Macro sql feels slow - please run this code and tell me your results!

Using Peter.C's revised code,

%macro timetst() ;
%do i = 1 %to 1000 ;
CREATE TABLE tabletest (test int) ;
DROP TABLE tabletest ;
%end ;
%mend timetst ;

proc sql noprint;
%timetst() ;
quit;

my result using EG4.1 over citrix,sas 9.1.3 windows 2003 at work is

NOTE: PROCEDURE SQL used (Total process time):
real time 1.37 seconds
cpu time 1.39 seconds

with eg4.1 , sas 9.1.3 running thru vmware server, (server 2003) on a laptop dual core 2ghz with 1gb ram available, the same code results are

real time 3.31 seconds
cpu time 2.73 seconds

On the same machine using sas 9.1.3 sp4 DM
real time 2.64seconds
cpu time 2.54 seconds
PROC Star
Posts: 1,759

Re: Macro sql feels slow - please run this code and tell me your results!

Posted in reply to DerekAdams
Calling proc sql once is cheating Smiley Happy Peter's code yields 1.9s elapse and 1.8s CPU, so twice as fast.

I timed using
[pre]
%macro timetest();
%do i = 1 %to 1000;
proc sql noprint;
CREATE TABLE tabletest (test int);
DROP TABLE tabletest;
run;
%end;
%mend timetest;


%let a=%sysfunc(time());
%timetest()

%put %sysevalf(%sysfunc(time())-&a);
[/pre]
Valued Guide
Posts: 2,177

Re: Macro sql feels slow - please run this code and tell me your results!

is there any way to obtain the cpu time used over a group of steps (for example, from accumulated CPU time in session so far, which we only see in the log as the SAS session closes) ?
Super Contributor
Super Contributor
Posts: 3,174

Re: Macro sql feels slow - please run this code and tell me your results!

Explore using the ALTLOG system option at start-up along with the appropriate diagnostic output settings like FULLSTIME - also consider other SAS performance analysis techniques mentioned in this paper:

Solving SAS Performance Problems: Employing Host Based Tools
Tony Brown, SAS Institute Inc., Dallas, TX
http://support.sas.com/rnd/papers/sugi31/practicalperf.pdf


Scott Barry
SBBWorks, Inc.
Valued Guide
Posts: 2,177

Re: Macro sql feels slow - please run this code and tell me your results!

thanks for suggestion of the at paper. I'll have a look.
I haven't found fullstimer or altlog providing adequate precision to total the brief cpu times of the sql create and delete steps in these steps. That's why I was asking for something else. The time(), datetime() etc. functions enable calculation of (single-threading) wallclock times. For CPU times, I'm hoping the "practicalPerf" paper might come up with something like ARM (or log4sas).
thanks

PeterC unfortunately, I'm not finding what I wanted in that paper


Message was edited by: Peter.C
Ask a Question
Discussion stats
  • 11 replies
  • 357 views
  • 0 likes
  • 7 in conversation