Date:         Sun, 20 Feb 2005 17:08:12 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: RE : sql join vs sas merge
Comments: To: datametric@CLUB-INTERNET.FR
In-Reply-To:  <000001c5174e$e73fc050$0200a8c0@CENTRAL>
Content-Type: text/plain; format=flowed

Stephane,

I would agree that a join can only start to be called good if and only if
you get the desired resuilt.  However as I have started a study of the SQL
optimizer I have found that well speed when talking about SQL joins isn't as
easy as one thinks.  When joining two ro more tables in SQL a good
understanding (which I don't claim to have yet) of how to construct the
where clause is neccessary.  Also as Paul Kant pointed out in one of his
tech papers that (I think HOward showed him this) that by making the where
clause more complicated in certain situations will speed up the query.  As
will possibly using some macro code to create what I call a smart where
clause, and finally one could increase the buffersize to speed the query up.
  The SQL optimizer is a funny thing sometimes...

Toby Dunn




From: Datametric <datametric@CLUB-INTERNET.FR>
Reply-To: Datametric <datametric@CLUB-INTERNET.FR>
To: SAS-L@LISTSERV.UGA.EDU
Subject: RE : sql join vs sas merge
Date: Sun, 20 Feb 2005 14:20:09 +0100
Hi,

An efficient join is a good join : it have to give you the good results ;-)

But if you want to understand which kind of joins is faster than other, you
have to compare the CPU time and Elapsed time. What are these results ?

Anyway, you probably know the advantages of SQL join versus merge (and vice
versa). I'll invite you to read some papers about your question :

www2.sas.com/proceedings/sugi28/096-28.pdf

www2.sas.com/proceedings/sugi29/076-29.pdf

www2.sas.com/proceedings/sugi29/042-29.pdf


Stéphane.

(Oh, I've forgotten, thanks to Lex to have added the SUGI29 into his
site...)




-----Message d'origine-----
De : SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] De la part de Ben
Envoyé : samedi 19 février 2005 21:11
À : SAS-L@LISTSERV.UGA.EDU
Objet : sql join vs sas merge


*******************************;
data a;
input pid dt1 date7.;
datalines;
101 1jan04
201 1feb04
301 1mar05
401 1apr05
501 1apr05
601 1apr05
;
run;

data b;
input pid dt2 date7.;
datalines;
101 1feb04
401 1jun05
;
run;

data c;
input pid dt3 date7.;
datalines;
401 1jun05
501 1jun05
;
run;

data d;
input pid dt4 date7.;
datalines;
201 1jun05
;
run;

***merge****;
data x_merge;
merge a (in=a) b c d ;
by pid;
if a;
run;

***sql**join*****;

proc sql;
create table t1 as
select a.pid,a.dt1,b.dt2
from a full join b on
     a.pid=b.pid;

create table t2 as
select t.pid,t.dt1,t.dt2,c.dt3
from tmp1 as t full join c on
     t.pid=c.pid;

create table x_join as
select t.pid,t.dt1,t.dt2,t.dt3,d.dt4
from t2 as t full join d on
     t.pid=d.pid;


I wonder if there is a more efficient join?