Optimizing Your SQL Usage Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options 39
For better performance, it is recommended that the SAS dataset be smaller than the
DBMS table, otherwise processing can be extremely slow.
MULTI_DATASRC_OPT= generates a SELECT COUNT to determine the size of
non-SAS datasets. If you know the size of your dataset, you can use DBMASTER to
designate the larger table.
MULTI_DATASRC_OPT= might provide performance improvements over DBKEY=.
If both options are specified, DBKEY= overrides MULTI_DATASRC_OPT=.
MULTI_DATASRC_OPT= is only used when SAS is processing a join with PROC
SQL. It will not be used for SAS datastep processing. For certain joins operations, such
as those involving additional subsetting applying to the query, PROC SQL might
determine that it is more efficient to process the join internally. In these situations it
will not use the MULTI_DATASRC_OPT= optimization even when specified. If PROC
SQL determines it can pass the join directly to the DBMS it will also not use this option
even though it is specified.
In this example, the MULTI_DATASRC_OPT= option is used to improve the
performance of a SQL join statement. MULTI_DATASRC_OPT= instructs PROC SQL
to pass the WHERE clause to the SAS/ACCESS engine with an IN clause built from the
SAS table. The engine then passes this optimized query to the DBMS server. The IN
clause is built from the unique values of the SAS DeptNo variable. As a result, only
rows that match the WHERE clause are retrieved from the DBMS. Without this option,
PROC SQL retrieves all the rows from the Dept table and applies the WHERE clause
during PROC SQL processing in SAS. Processing can be both CPU-intensive and
I/O-intensive if the Oracle Dept table is large.
data keyvalues;
deptno=30;
output;
depno=10;
output;
run;
libname dblib oracle user=testuser password=testpass
path=’myorapath’ multi+datasrc_opt=in_clause;
proc sql;
select bigtab.deptno, bigtab.loc
from dblib.dept bigtab,
keyvalues smallds
where bigtab.deptno=smallds.deptno;
quit;
The SQL statement that is created by SAS/ACCESS and passed to the DBMS is
similar to the following
SELECT "DEPTNO", "LOC" FROM DEPT WHERE (("DEPNO" IN (10,30)))
Using DBKEY or DBINDEX decreases performance when the SAS data set is too
large. These options cause each value in the transaction data set to generate a new
result set (or open cursor) from the DBMS table. For example, if your SAS data set has
100 observations with unique key values, you request 100 result sets from the DBMS,
which may be very expensive. You must determine whether use of these options is
appropriate, or whether you can achieve better performance by reading the entire
DBMS table (or by creating a subset of the table).
DBINDEX= and DBKEY= are mutually exclusive. If you specify them together,
DBKEY= overrides DBINDEX=. Both of these options are ignored if you specify the
SAS/ACCESS data set option DBCONDITION= or the SAS data set option WHERE=.
