33604 - PROC SQL Join Algorithms and the

Sample 33604: PROC SQL Join Algorithms and the _METHOD Option

When it comes to performing PROC SQL joins, users supply the list of tables for joining along with the join conditions, and the PROC SQL optimizer has the task of determining which of the available join algorithms to use for performing the join operation. There are three basic algorithms used in joining:

Nested Loop Join – When an equality condition is not specified, a read of the complete contents of the right table is processed for each row in the left table.
Merge Join – When the tables specified are already in the desired sort order, resources will not need to be extended to rearranging the tables.
Hash Join – When an equality relationship exists, the smaller of the tables is able to fit in memory, no sort operations are required, and each table is read only once.

Under Version 8.2 or later, PROC SQL supports a powerful “undocumented” option called _METHOD. Although undocumented features like the _METHOD option should be used with caution, SAS users may find this option to provide far greater value than risk. In fact, the _METHOD option is worth exploring because the benefits associated with gaining a better understanding associated with what happens during specific PROC SQL processes, including joins.

The various codes and their corresponding descriptions associated with the _METHOD option appear in the table below.

METHOD Option Codes and Descriptions

image label

The following PROC SQL code example, and corresponding SAS Log, illustrates the results from running the _METHOD option with a simple two-way equi-join. The _METHOD option displays information that can help users better understand, as well as tune and debug their join queries. The SAS Log illustrates that the two-source tables are MOVIES and ACTORS, and the join algorithm used by the PROC SQL optimizer is a hash join. Because a hash join utilizes available real memory to perform the join, it is often faster than a merge or index-join operation because of the speed of real memory as well as no sort operation on the source tables being required.

PROC SQL Code

PROC SQL  _METHOD;
  SELECT  MOVIES.TITLE, RATING, ACTOR_LEADING
    FROM MOVIES,
         ACTORS
      WHERE MOVIES.TITLE = ACTORS.TITLE;
QUIT;

SAS Log Results

NOTE: SQL execution methods chosen are:
      sqxslct
          sqxjhsh
              sqxsrc( MOVIES )
              sqxsrc( ACTORS )

About the Author
Kirk Paul Lafler is the author of PROC SQL: Beyond the Basics Using SAS, published by SAS Press. He also writes the popular SAS tips column "Kirk's Korner," which appears regularly in several SAS users group newsletters, and is a frequent speaker at SAS users group meetings.

His book is available from the online bookstore.

PROC SQL: Beyond the Basics Using SAS

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Date Modified:	2008-10-14 14:39:19
Date Created:	2008-10-14 13:44:42

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	z/OS
		OpenVMS VAX
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		OS/2
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows XP Professional
		Windows Millennium Edition (Me)
		Windows Vista
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX

Support

Sample 33604: PROC SQL Join Algorithms and the _METHOD Option

Operating System and Release Information