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

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

MBI — Tue, 26 May 2009 23:28:41 GMT

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()

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

Doc_Duke — Wed, 27 May 2009 01:36:19 GMT

About 6.6 seconds of cpu time. Run twice.

batch SAS 9.2, Phase 2. Windows XP SP 2, Lenovo T61p laptop.

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

Peter_C — Wed, 27 May 2009 13:04:50 GMT

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

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

deleted_user — Wed, 27 May 2009 23:29:06 GMT

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

~ Sukanya E

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

DerekAdams — Wed, 27 May 2009 23:41:21 GMT

0.01 seconds using EG4.1 on citrix

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

ChrisNZ — Thu, 28 May 2009 03:48:17 GMT

3.4 s elapse on
SAS (r) 9.1 (TS1M3) DMS
XP_PRO SP2
E8500 @ 3.16 GHz

Peter, do something about your PC! 🙂

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

Peter_C — Thu, 28 May 2009 09:45:12 GMT

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

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

DerekAdams — Thu, 28 May 2009 22:04:49 GMT

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

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

ChrisNZ — Thu, 28 May 2009 22:26:43 GMT

Calling proc sql once is cheating 🙂 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]

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

Peter_C — Fri, 29 May 2009 11:00:35 GMT

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) ?

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

sbb — Fri, 29 May 2009 12:06:40 GMT

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.

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

Peter_C — Fri, 29 May 2009 12:53:14 GMT

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