Tests for small tables (10K obs,) are below. I don't have space for bigger sets on my laptop.
In terms of "real time" it is: 1) SQL (even done by SQXJSL = step loop join) , 2) hash, 3) point (even when whole table is in ram).
In terms of "memory" it is: 1) point, 2) hash, 3) SQL.
In terms of "OS memory" everything was less than 30MB so...
Bart
1 options msglevel=I fullstimer;
2 data x;
3 do x = 1 to 1e4;
4 x1 = x;
5 x2 = x;
6 x3 = "a";
7 output;
8 end;
9 run;
NOTE: The data set WORK.X has 10000 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 395.03k
OS Memory 24060.00k
10
11 data y;
12 do y = 1 to 1e4;
13 y1 = y;
14 y2 = y;
15 y3 = "b";
16 output;
17 end;
18 run;
NOTE: The data set WORK.Y has 10000 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 395.03k
OS Memory 24060.00k
19
20 proc sql;
21 create table test1 as
22 select x.*, y.* from x,y
23 ;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: Table WORK.TEST1 created, with 100000000 rows and 8 columns.
24 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 8.60 seconds
user cpu time 6.51 seconds
system cpu time 1.82 seconds
memory 5633.68k
OS Memory 29184.00k
25 data test2;
26 set x;
27 do point= 1 to nobs;
28 set y nobs=nobs point=point;
29 output;
30 end;
31 run;
NOTE: There were 10000 observations read from the data set WORK.X.
NOTE: The data set WORK.TEST2 has 100000000 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 22.25 seconds
user cpu time 15.43 seconds
system cpu time 6.62 seconds
memory 930.18k
OS Memory 24572.00k
32 data test3;
33 if _N_ = 1 then do;
34 if 0 then set y;
35 dcl hash h(dataset : "y");
36 h.definekey("y");
37 h.definedata(all : "Y");
38 h.definedone();
39 dcl hiter i("h");
40 end;
41
42 set x;
43
44 do while (i.next() = 0);
45 output;
46 end;
47 run;
NOTE: There were 10000 observations read from the data set WORK.Y.
NOTE: There were 10000 observations read from the data set WORK.X.
NOTE: The data set WORK.TEST3 has 100000000 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 13.32 seconds
user cpu time 11.21 seconds
system cpu time 2.03 seconds
memory 2446.78k
OS Memory 26068.00k
48 sasfile y load;
NOTE: The file WORK.Y.DATA has been loaded into memory by the SASFILE statement.
49 data test4;
50 set x;
51 do point= 1 to nobs;
52 set y nobs=nobs point=point;
53 output;
54 end;
55 run;
NOTE: There were 10000 observations read from the data set WORK.X.
NOTE: The data set WORK.TEST4 has 100000000 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 22.38 seconds
user cpu time 16.56 seconds
system cpu time 5.81 seconds
memory 713.75k
OS Memory 25408.00k
56 sasfile y close;
NOTE: The file WORK.Y.DATA has been closed by the SASFILE statement.
SQL "under the hood":
1 proc sql feedback _tree _method;
2 create table test1 as
3 select x.*, y.* from x,y
4 ;
NOTE: Statement transforms to:
select X.x, X.x1, X.x2, X.x3, Y.y, Y.y1, Y.y2, Y.y3
from WORK.X, WORK.Y;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: SQL execution methods chosen are:
sqxcrta
sqxjsl
sqxsrc( WORK.X )
sqxsrc( WORK.Y )
Tree as planned.
/-SYM-V-(x.x:1 flag=00000001)
/-OBJ----|
| |--SYM-V-(x.x1:2 flag=00000001)
| |--SYM-V-(x.x2:3 flag=00000001)
| |--SYM-V-(x.x3:4 flag=00000001)
| |--SYM-V-(y.y:1 flag=00000001)
| |--SYM-V-(y.y1:2 flag=00000001)
| |--SYM-V-(y.y2:3 flag=00000001)
| \-SYM-V-(y.y3:4 flag=00000001)
/-JOIN---|
| | /-SYM-V-(x.x:1 flag=00000001)
| | /-OBJ----|
| | | |--SYM-V-(x.x1:2 flag=00000001)
| | | |--SYM-V-(x.x2:3 flag=00000001)
| | | \-SYM-V-(x.x3:4 flag=00000001)
| | /-SRC----|
| | | \-TABL[WORK].x opt=''
| \-FROM---|
| | /-SYM-V-(y.y:1 flag=00000001)
| | /-OBJ----|
| | | |--SYM-V-(y.y1:2 flag=00000001)
| | | |--SYM-V-(y.y2:3 flag=00000001)
| | | \-SYM-V-(y.y3:4 flag=00000001)
| \-SRC----|
| \-TABL[WORK].y opt=''
--SSEL---|
NOTE: Table WORK.TEST1 created, with 100000000 rows and 8 columns.
5 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 8.52 seconds
user cpu time 6.43 seconds
system cpu time 1.87 seconds
memory 5647.87k
OS Memory 30464.00k
