Optimizing Your SQL Usage

When Passing Joins to the DBMS Will Fail

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, the join would then be performed internally by PROC SQL. 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 upon 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. The following are the primary reasons why join statements might fail to be passed:

The generated SQL syntax is not accepted by the DBMS.

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.
The use of data set options in the query.

The specification of 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.
The use of certain LIBNAME options.

The specification of LIBNAME options that request member level controls, such as table locks (READ_LOCK_TYPE= LIBNAME Option or UPDATE_LOCK_TYPE= LIBNAME Option), prohibits the statement from successfully passing to the DBMS for direct processing.
The DIRECT_SQL= LIBNAME Option option setting.

The DIRECT_SQL= option default setting is YES. PROC SQL attempts to pass SQL joins directly to the DBMS for processing. Other settings for the DIRECT_SQL= option 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.

DIRECT_SQL=YES

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.

DIRECT_SQL=NO

PROC SQL does not attempt to pass SQL join queries to the DBMS. Other SQL statements can be passed, however. If the is in effect, the generated SQL can also be passed.

DIRECT_SQL=NONE

PROC SQL does not attempt to pass any SQL directly to the DBMS for processing.

DIRECT_SQL=NOWHERE

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.

DIRECT_SQL=NOFUNCTIONS

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.

DIRECT_SQL=NOGENSQL

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= 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.
Using of SAS functions on the SELECT clause can prevent joins from being passed.

Top of Page