Date:         Wed, 3 Sep 2008 10:55:49 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Hash lookup not quicker than SQL inner join?
Comments: To: Sander Burggraaff <info@BURGIE-T.NL>
In-Reply-To:  <e9869df5-cf8b-49eb-9a83-7d8811e607de@a1g2000hsb.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Sander:
Yes, a SAS SQL inner join will often when appropriate create a hash index in the background and use it to select specific key values. A SQL compiler develops an execution plan that improves where possible on brute force searches, sorting, etc. Unfortunately SAS SQL does not always find better solutions, as I have found with left joins and with summaries.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Sander Burggraaff
Sent: Wednesday, September 03, 2008 6:16 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Hash lookup not quicker than SQL inner join?


On 2 sep, 20:09, jay.so...@GMAIL.COM (Jay Soule) wrote:
> On Tue, Sep 2, 2008 at 9:00 AM, Bucher Scott <SBuc...@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


Oh yes, that's right! I hadn't thought of that and I should have remembered... OK, thanks a lot!


Sander