Date:         Wed, 18 Dec 2002 17:54:06 -0600
Reply-To:     pudding_man@lycos.com
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Puddin' Man <pudding_man@LYCOS.COM>
Organization: Lycos Mail  (http://www.mail.lycos.com:80)
Subject:      Re: SQL Help- w/o sort - V9
Comments: To: sashole@bellsouth.net
Content-Type: text/plain; charset=us-ascii

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