Date: Sat, 2 Feb 2008 13:29:08 -0500
Reply-To: Muthia Kachirayan <muthia.kachirayan@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Muthia Kachirayan <muthia.kachirayan@GMAIL.COM>
Subject: Re: PROC SQL with alternate keys
Content-Type: text/plain; charset=ISO-8859-1
On Feb 2, 2008 10:45 AM, Paul <firstname.lastname@example.org> wrote:
> I'm trying to join a small table A to a large table B where A.key can
> join B.key1 or (rarely) B.key2. Including key2 in the join slows the
> query substantially. That is.
> (1) select * from A inner join B where A.key=B.key1
> is pretty fast, but
> (2) select * from A inner join B where (A.key=B.key1 or A.key=B.key2)
> is much more than twice as slow.
> I don't know what's going on, but I'm thinking query (2) might not be
> optimized for the fact that table A is much smaller than table B.
> Any tips on speeding performance of query (2) would be most
Following Gregg, here is a Key Indexing method. Unlike HASH solution, Key
Indexing depends on a numeric value to look up the ARRAY( K[ ] ). If your
KEY takes value not more than 9-digit number, then your system can find
sufficient memory space.
In the following code I assume your key takes a maximum value of 50 million.
%let num = 50000000;
array k[&num] _temporary_;
set SMALL end = eofS;
k[key] = 1;
set LARGE end = eofL;
if k[key1] or k[key2] then output;