Date: Tue, 2 Sep 2008 11:09:09 -0700
Reply-To: Jay Soule <jay.soule@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jay Soule <jay.soule@GMAIL.COM>
Subject: Re: Hash lookup not quicker than SQL inner join?
Content-Type: text/plain; charset=ISO-8859-1
On Tue, Sep 2, 2008 at 9:00 AM, Bucher Scott <SBucher@schools.nyc.gov> wrote:
> You could try the "PROC SQL _METHOD;" or "PROC SQL _TREE;" options to
> get more details on how SQL is actually performing the joins. In some
> cases, SQL actually performs a hash join. I believe there are one or two
> conference papers on this topic and the aforementioned options.
Right - if the note in the SAS log after the SQL step shows that the
method used was "sqlxjhsh" then SAS used a hash join.
- Jay Soule
> Sander Burggraaff
> Sent: Tuesday, September 02, 2008 11:42 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Hash lookup not quicker than SQL inner join?
> I always assumed that a hash lookup would be a quicker way to join two
> tables than a SQL join. But I recently did some testing and the
> results were not what I expected.
> Let's say we have two tables: Table_1 and Table_2. These two tables
> have 4 variables in common: B, C, D and E. B is a character variable
> with a length of 20 and the other 3 are all numeric with a length of
> 8. Table_1 also has a variable called A1 and Table_2 has a variable
> called A2.
> Of course the variables B, C, D and E in both tables are keys we want
> to join the tables on. The result table will hold the variables A1 and
> Table_1 holds the same variables as Table_2. At first we tried
> 25.000.000 observations but we ran out of memory when using the Hash
> lookup. Apparently the 1.6Gb memory available was not enough to store
> the hash table in. So we lowered the amount of observations to
> To our surprise the inner join performed just as quickly as the hash
> lookup. I expected SAS to start sorting when using the inner join but
> apparently it doesn't. During some tests it even looked like the inner
> join was quicker than the hash lookup.
> Can anyone explain why the inner join is just as quick? No sorting?
> I searched this discussion group but couldn't find any postings about
> it. Have older posts been deleted?