proc sql query 1 vs query 2

[jplarios]

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;

[Doc_Duke]

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

[ballardw]

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