By default,
SAS/ACCESS tries to pass certain types of
SQL statements directly to the DBMS for processing. Most notable are
SQL join statements that would otherwise be processed as individual
queries to each data source that belonged to the join. In that instance,
PROC SQL would then perform the join internally. Passing the join
to the DBMS for direct processing can result in significant performance
gains.
However, there are several
reasons why a join statement under PROC SQL might not be passed to
the DBMS for processing. In general, the success of the join depends
on the nature of the SQL that was coded and the DBMS's acceptance
of the generated syntax. It is also greatly influenced by the use
of option settings. Here are the primary reasons why join statements
might fail to be passed.
The DBMS does not accept the generated SQL syntax.
PROC SQL attempts to
pass the SQL join query directly to the DBMS for processing. The DBMS
can reject the syntax for any number of reasons. In this event, PROC
SQL attempts to open both tables individually and perform the join
internally.
The SQL query involves multiple librefs that do not share connection
characteristics.
If the librefs are
specified using different servers, user IDs, or any other connection
options, PROC SQL does not attempt to pass the statement to the DBMS
for direct processing.
Using data set options in the query
Specifying any data
set option on a table that is referenced in the SQL query prohibits
the statement from successfully passing to the DBMS for direct processing.
Using certain LIBNAME options
Using SAS functions on the SELECT clause
Specifying SAS functions
on the SELECT clause can prevent joins from being passed.
Using the DIRECT SQL= LIBNAME option setting.
The DIRECT SQL= LIBNAME option default setting is YES. PROC SQL
attempts to pass SQL joins directly to the DBMS for processing. Other
settings for DIRECT_SQL= influence the nature of the SQL statements
that PROC SQL tries to pass down to the DBMS or if it tries to pass
anything at all.
PROC SQL automatically
attempts to pass the SQL join query to the DBMS. This is the default
setting for this option. The join attempt could fail due to a DBMS
return code. If this happens, PROC SQL attempts to open both tables
individually and perform the join internally.
PROC SQL does not attempt
to pass SQL join queries to the DBMS. However, other SQL statements
can be passed. If the
MULTI_DATASRC_OPT= LIBNAME Option is in effect,
the generated SQL can also be passed.
PROC SQL does not attempt
to pass any SQL directly to the DBMS for processing.
PROC SQL attempts to
pass SQL to the DBMS including SQL joins. However, it does not pass
any WHERE clauses associated with the SQL statement. This causes any
join that is attempted with direct processing to fail.
PROC SQL does not pass
any statements in which any function is present to the DBMS. Normally
PROC SQL attempts to pass down any functions coded in the SQL to the
DBMS, provided the DBMS supports the given function.
PROC SQL does not attempt
to pass SQL join queries to the DBMS. Other SQL statements can be
passed down, however. If the
MULTI_DATASRC_OPT= LIBNAME Option is in effect,
the generated SQL can be passed.
DIRECT_SQL=NOMULTOUTJOINS
PROC SQL does not attempt
to pass any multiple outer joins to the DBMS for direct processing.
Other SQL statements can be passed, however, including portions of
a multiple outer join.