Query performance is
optimized when the key column occurs in ...
|
Example
|
---|---|
a WHERE clause expression
that contains
|
proc sql;
select empid, jobcode, salary
from sasuser.payrollmaster
where jobcode='FA3'
order by empid; Key Column(s): JobCode
|
a subquery returning
values to the IN operator.
|
proc sql; select empid, lastname, firstname, city, state from sasuser.staffmaster where empid in (select empid from sasuser.payrollmaster where salary>40000); Key Column(s): EmpID
|
a correlated subquery,
in which the column being compared with the correlated reference is
indexed
|
proc sql; select lastname, firstname from sasuser.staffmaster where 'NA'= (select jobcategory from sasuser.supervisors where staffmaster.empid = supervisors.empid); Key Column(s): Supervisors.EmpID
|
a join in which
|
proc sql; select * from sasuser.payrollmaster as p, sasuser.staffmaster as s where p.empid = s.empid order by jobcode; Key Column(s): Payrollmaster.EmpID
or Staffmaster.EmpID
|
proc sql;
select empid, jobcode, salary
from work.payrollmaster
where jobcode='FA3'
order by empid;
FA3
,
then the use of an index does not significantly improve the efficiency
of the following query. In fact, performance might be degraded.
FA3
, then
PROC SQL can process the query more efficiently by using the index.
Male
and Female
).