As a rule of thumb:
- not using functions will speed up the query
- a join is faster than a in (select ..) clause (depending in the existence of an index and the cardinality of the subset)
data TEST;
length A $16;
do I=1 to 1e7;
A=put(ranuni(1)*1e16, 16.0);
output;
end;
run;
proc sql;
create table T as select * from TEST where A eqt '22';
quit;
proc sql; * real time 1.46 seconds;
create table S as
select TEST.*
from TEST
where A in (select A from T);
proc sql; * real time 0.81 seconds;
create table S as
select TEST.*
from TEST, T
where TEST.A=T.A;
quit;
Also, put the most discriminant filters first to reduce volume as soon as possible.
71 proc sql;
72 create table T as select * from TEST where A like '2%' and A like '222222%';
NOTE: Table WORK.T created, with 10 rows and 2 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 1.42 seconds
73 proc sql;
74 create table T as select * from TEST where A like '222222%' and A like '2%';
NOTE: Table WORK.T created, with 10 rows and 2 columns.
75 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.26 seconds
... View more
