Date:         Wed, 9 Mar 2011 14:56:22 -0600
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: SQL lookup - more efficient way?
Comments: To: Michael Murff <mysasbox@gmail.com>
In-Reply-To:  <AANLkTinhnRJPkvofLrjbwZfNY5wxTfoLNuxqG0P8PpM1@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Do you have any indexes on the tables?

I think BETWEEN is technically slower than two < > criteria, at least on
some SQL instances; I'm not sure why that is, though perhaps > and then <
simply disqualifies records faster (some records fail the first criteria and
do not have to evaluate the second). I do agree with Bob that a correlated
subquery may be more efficient (depending on the relative size of the
tables).

-Joe

On Wed, Mar 9, 2011 at 12:34 PM, Michael Murff <mysasbox@gmail.com> wrote:

> ** Hi sas-l;
> ** my real tables are quite large;
> ** anybody know how to optimize this using ansi standard SQL syntax? ;
>
> data main;
>  do id=1 to 10;
>   num_x=round(100*uniform(123),1); output;
>  end;
> run;
>
> data lookup;
>  beg_num=1; end_num=10; output;
>  beg_num=11; end_num=20; output;
>  beg_num=21; end_num=30; output;
> run;
> proc sql;
>  select * from main, lookup
>  where num_x between beg_num and end_num
>  order by id;
> quit;
> endsas;
>
> ** thank you in advance;
> ** Regards, Mike;
>