Advanced PROC SQL Optimization Hints

Overview

Most users never need to use the options that are described in this topic. However, there are some cases when slightly different SQL query generation can lead to better query performance. These query modifications might be dependent on your data, such as when performing a join operation that depends on the relative size of the tables that you are joining.

The options that are described in these sections are considered to be hints. That is, PROC SQL might ignore these options if it determines that the options cannot be used to pass down a query.

MULTI_DATASRC_OPT=

The MULTI_DATASRC_OPT= LIBNAME option is a hint to the PROC SQL optimizer. Specifying MULTI_DATASRC_OPT=IN_CLAUSE instructs the PROC SQL optimizer to generate an IN clause for a join of two tables. This prevents SAS from retrieving all rows from the DBMS tables. This hint is specific to PROC SQL and does not apply to DATA step processing.

This option typically improves performance when these conditions are true:

• One of the tables is small enough to be a candidate for IN clause processing. This depends on the number of unique values that are allowed for IN clause processing for your DBMS.
• The other table is significantly larger than the smaller table.
• The JOIN condition is not passed to the underlying database. For more information, see When Passing Joins to the DBMS Fails.

Because the MULTI_DATASRC_OPT= LIBNAME option acts as a hint, it might be ignored by PROC SQL.

When you join two DBMS tables, MULTI_DATASRC_OPT= performs a row count operation on each table to determine the larger table. If you already know which table is larger, indicate that with the DBLARGETABLE= data set option. When you use the DBLARGETABLE= data set option, SAS does not have to compute which table is larger.