I am running some tests in SAS to check if my program can be optimized. I have three ways of reaching the same goal, and it seems like my third way is much faster (20 sec vs 2 min). However, when I rerun all methods, then they all only take around 20 sec. I found that this could be due to the cache in memory: https://stackoverflow.com/questions/21149242/why-does-a-second-run-of-the-same-code-execute-faster/21149294#21149294 Now I wonder if my third attempt just runs faster because sas translates it to the same query as the second, and can re-use some information from that run. I have tried closing SAS, or cleaning the work (lib in which I work) but it does not seem to reset the memory, i.e. I can not recreate the longer time from the first run. What does seem to clear the memory is run an unrelated query and then re-run my three queries, but this is not consistent and does not always work. Is there a way to test all queries under the same conditions (I am already dealing with variation due to server load, that is why I like to rerun a few times)? Below is a simplified version of the queries I am testing, but my data has more observations. proc sql;
create table test1 as
select distinct t1.Name
from sashelp.class t1
inner join sashelp.classfit t2
on t1.Name = t2.Name
where t1.Sex = "F"
and t2.age between 12 and 15;
quit;
proc sql;
create table test2 as
select distinct t1.Name
from sashelp.class t1
where t1.Sex = "F"
and t1.Name in (select distinct t2.Name from sashelp.classfit t2 where t2.age between 12 and 15);
quit;
proc sql;
create table test3 as
select distinct t1.Name
from sashelp.classfit t1
where t1.age between 12 and 15
and t1.Name in (select distinct t2.Name from sashelp.class t2 where t2.Sex = "F");
quit;
... View more
