BookmarkSubscribeRSS Feed
MBI
Calcite | Level 5 MBI
Calcite | Level 5
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()
11 REPLIES 11
Doc_Duke
Rhodochrosite | Level 12
About 6.6 seconds of cpu time. Run twice.

batch SAS 9.2, Phase 2. Windows XP SP 2, Lenovo T61p laptop.
Peter_C
Rhodochrosite | Level 12
1 min 17.5secs on a busy 32bit win-XP machine with 4gb memory, running SAS9.1.3-sp4 Display Manager
deleted_user
Not applicable
10 secs 1.86GHz Processor, 1.5GB RAM, Windows XP.

~ Sukanya E
DerekAdams
Calcite | Level 5
0.01 seconds using EG4.1 on citrix
ChrisNZ
Tourmaline | Level 20
3.4 s elapse on
SAS (r) 9.1 (TS1M3) DMS
XP_PRO SP2
E8500 @ 3.16 GHz

Peter, do something about your PC! 🙂
Peter_C
Rhodochrosite | Level 12
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
DerekAdams
Calcite | Level 5
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
ChrisNZ
Tourmaline | Level 20
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]
Peter_C
Rhodochrosite | Level 12
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) ?
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1531 views
  • 0 likes
  • 7 in conversation