Download SAS Data Integration Studio 3.4: User's Guide

Working with the SQL Join Transformation
Tasks
323
Tasks
Sort-Merge Joins
Conditions often cause the SAS SQL optimizer to choose the sort-merge algorithm,
and techniques that improve sort performance also improve sort-merge join
performance. However, understanding and using index and hash joins can provide
performance gains. Sort-merge is the algorithm most often selected by the SQL
optimizer. When index nested loop and hash join are eliminated as choices, a
sort-merge join or simple nested loop join is used. A sort-merge sorts one table, stores
the sorted intermediate table, sorts the second table, and finally merges the two to form
the join result. Use the Suggest Sort Merge Join property on the SQL Properties
pane to encourage a sort-merge. This property adds MAGIC=102 to the PROC SQL
invocation, as follows: proc sql _method magic=102;.
Index Joins
An index join looks up each row of the smaller table by querying an index of the
large table. When chosen by the optimizer, an index join usually outperforms a
sort-merge join on the same data. To get the best join performance, you should ensure
that both tables have indexes created on any columns that the you want to participate
in the join relationship. The SAS SQL optimizer considers an index join when:
3 The join is an equijoin in which tables are related by equivalence conditions on key
columns.
3 Joins with multiple conditions are connected by the AND operator.
3 The larger table has an index composed of all the join keys.
Encourage an index nested loop with IDXWHERE=YES as a data set option, as follows:
proc sql _method; select ... from smalltable, largetable(idxwhere=yes).
You can also turn on the Suggest Index Join property on the properties panes for the
tables in the query.
Hash Joins
The optimizer considers a hash join when an index join is eliminated as a possibility.
With a hash join, the smaller table is reconfigured in memory as a hash table. SQL
sequentially scans the larger table and performs row-by-row hash lookup against the
small table to form the result set. A memory-sizing formula, which is not presented
here, determines whether a hash join is chosen. The formula is based on the PROC SQL
option BUFFERSIZE, whose default value is 64 KB. On a memory-rich system, consider
increasing BUFFERSIZE to increase the likelihood that a hash join is chosen. You can
also encourage a hash join by increasing the default 64 KB PROC SQL buffersize
option. Set the Buffer Size property on the SQL Properties pane to 1048576.