Debugging an SQL Query :: SAS(R) Data Integration Studio 4.5: User's Guide

Problem

You want to determine which join algorithm is selected for an SQL query by the SAS SQL Optimizer. You also need to know how long it takes to run the job that contains the SQL Join transformation.

Solution

You can enable debugging for the query by setting the Debug property in the SQL Properties pane. Perform the following tasks:

Tasks

Set the Debug Property

The Debug property in the SQL Properties pane enables the following debugging option:

options sastrace = ',,,sd' sastraceloc = saslog
no$stsuffix fullstimer;

You can use this option to determine which join algorithms are used in the query and to get timing data for the SAS job.

You can use the keywords from the trace output that are listed in the following table to determine which join algorithm was used:

Debugging Keywords and Join Algorithms

Keyword	Join Algorithm
sqxsort	sort step
sqxjm	sort-merge join
sqxjndx	index join
sqxjhsh	hash join
sqxrc	table name

Examine Some Sample Method Traces

The following sample fragments illustrate how these keywords appear in a _method trace.

In the first example, each data set is sorted and sort-merge is used for the join:

sqxjm
    sqxsort
        sqxsrc( WORK.JOIN_DATA2 )
    sqxsort
        sqxsrc( LOCAL.MYDATA )

In the next example, an index nested loop is used for the join:

sqxjndx
    sqxsrc( WORK.JOIN_DATA2 )              
    sqxsrc( LOCAL.MYDATA )

In the final example, a hash is used for the join:

sqxjhsh              
    sqxsrc( LOCAL.MYDATA )              
    sqxsrc( WORK.JOIN_DATA1 )