Most instructional ...

On Tue, 17 Dec 2002 17:10:05 Paul M. Dorfman wrote: >> -----Original Message----- >> From: Puddin' Man [mailto:pudding_man@lycos.com] >> >> On Tue, 17 Dec 2002 06:30:16 >> Paul Dorfman wrote: >> >> >the presence of more than one processor induces a >> >parallel processing sort, >> >> Parallel cpu? Parallel i/o? Both?? :-) > >CPU at least (irrelevant/self-evident notes dropped): > >97 option nofullstimer ; >98 data a ; >99 retain a 1 b 2 c 'cccc' ; >100 do var = 1e6 to 1 by -1 ; >101 output ; >102 end ; >103 run ; > >NOTE: The data set WORK.A has 1000000 observations and 4 variables. > >104 proc sort threads data = a out = threaded ; by var ; run ; > >NOTE: SAS threaded sort was used. >NOTE: PROCEDURE SORT used (Total process time): > real time 1.93 seconds > cpu time 3.51 seconds > >105 proc sort nothreads data = a out = nonthreaded ; by var ; run ; > >NOTE: SAS sort was used. >NOTE: PROCEDURE SORT used (Total process time): > real time 2.06 seconds > cpu time 2.03 seconds > >Notice the relationship between the CPU and real time in both cases.

Hmmmmmm. By the wall clock, it was 100*((2.06-1.93)/2.06) ~= 6.3% faster?

"WHOTCHOO MEAN 6% FASTER? HE'S GOT 2 CPU'S, DON'T HE? DON'T IT GOTTA BE TWICE AS FAST???" <g>

>> "Sorting is usually very much an i/o-bound process ...". > >Depending on the algorithm, memory resources, etc., but in the real life >mostly true...

Might even explain certain differential phenomena ...

>> >while with a background SQL sort, it does not seem >> >to be guaranteed. > >That is what I said... > >> Say you gotta 2 cpu desktop, W2k/XP, fully SMP enabled, dual >> i/o channels, no Syncsort. "Production" V9 (configured for >> parallel stuff) is supposed to utilize parallel facilities >> for PROC SORT but not for ORDER BY ? > >That is not what I said...

OK, but I remain uncertain as to what you _did_ say ... <g>

>106 proc sql threads ; >106! create table threaded as select * from a order by var ; >106! quit ; >NOTE: PROCEDURE SQL used (Total process time): > real time 3.06 seconds > cpu time 4.73 seconds > >107 proc sql nothreads ; >107! create table nonthreaded as select * from a order by var ; >107! quit ; >NOTE: PROCEDURE SQL used (Total process time): > real time 3.29 seconds > cpu time 3.20 seconds > >> Wouldn't both use the SAS sort routine?? > >Yes, they would. > >> Not certain why there would be a difference... > >There is none.

The "real time" for SORT is around 2 secs. For SQL it is around 3 secs. This is ~ 50% increase?

Running the same code on W2kP 8.1 I get about 100% increase or so (code after sig). Very similar with z/OS 8.2.

Howcum? SQL overhead for compile/interpretation??

>> It's probably too early to be asking such questions ... :-( > >I beg to differ ;-).

You are, of course, at least partially correct. But: 1.) You lack the HW/SW/config to test V8 parallel i/o? (correct po' me as necessary, specifying platform characteristics). 2.) Parallel performance _could_ undergo material change between your "Omega" and the "Production" V8.

Is there a long time lapse between your "Omega" and the projected "Production" V8? Wonder what those SI fellas/gals are up to alla that time? They gotta be doin' more'n just chompin' M&M's? Eh?? :-)

>> Now you've placed V9 firmly on the grill <g>, you've had the >> alpha/beta running for some time? > >My guess would be closer to omega... > >At home? At work? Do you use it for everyday processing? > >Pretty much so, yeah. > >> Is it your impression that your V9 is 100% adequate for the kind of >processing that sas-l folk commonly use V8 for? > >Yep. > >> Or do you observe "little funny thangs"? > >It is my duty to observe them. I have noticed some, but none in the >backward-compatible, plain-jane stuff.

Good. This is useful and welcome info. Danke, danke.

Zalut, Puddin'

****************************************************** *** Puddin' Man *** Pudding_Man@lycos.com ******** ******************************************************;

29 30 option nofullstimer ; 31 data a ; 32 retain a 1 b 2 c 'cccc' ; 33 do var = 1e6 to 1 by -1 ; 34 output ; 35 end ; 36 run ;

NOTE: The data set WORK.A has 1000000 observations and 4 variables. NOTE: DATA statement used: real time 3.10 seconds cpu time 0.73 seconds

37 38 proc sort data = a out = threaded ; by var ; run ;

NOTE: SAS sort was used. NOTE: There were 1000000 observations read from the data set WORK.A. NOTE: The data set WORK.THREADED has 1000000 observations and 4 variables. NOTE: PROCEDURE SORT used: real time 6.11 seconds cpu time 3.71 seconds

39 40 proc sql ; 41 create table threaded as select * from a order by var ; NOTE: Table WORK.THREADED created, with 1000000 rows and 4 columns.

42 quit ; NOTE: PROCEDURE SQL used: real time 14.28 seconds cpu time 5.70 seconds

_____________________________________________________________ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plus&ref=lmtplus