topic proc sql query 1 vs query 2 in SAS Procedures

proc sql query 1 vs query 2

jplarios — Fri, 25 Mar 2011 15:58:54 GMT

HI:
I am wandering why would the first take a lot longer then the second query in proc sql , the difference is noted in NOTE DIFFERENCE comment.

:
/*Query 1 */
proc sql;

select a.id,
b.name

from transactions as a ,
person as b
where
a.id = b.id
and
b.seq = (
select max(sub.seq)
from person as sub
where b.id = sub.id /*NOTE DIFFERENCE*/
)
quit;

/*Query 2 */
proc sql;

select a.id,
b.name

from transactions as a ,
person as b
where
a.id = b.id
and
b.seq = (
select max(sub.seq)
from person as sub
where a.id = sub.id /*NOTE DIFFERENCE*/

)
quit;

Re: proc sql query 1 vs query 2

Doc_Duke — Fri, 25 Mar 2011 17:07:08 GMT

There are a lot of POSSIBLE reasons that are data dependent. Without knowing the data, we are shooting in the dark. Possible reasons include:
-- size of data set a vs b.
-- indexing on a vs b.
-- location of a vs b (e.g. SAS dataset or Oracle database).
-- data set type (view vs dataset)

Doc Muhlbaier
Duke

Re: proc sql query 1 vs query 2

ballardw — Mon, 28 Mar 2011 21:02:57 GMT

As a minor addition to Docs comment on location: network drive vs local if your network runs like mine.