Important SPD Server SQL Planner Options :: SAS(R) Scalable Performance Data Server 5.1: User's Guide, Second Edition

_method

The SQL _method option is one of the most important reset options. The _method reset option provides a method tree in the output that shows how the SQL was planned and executed.

The following methods are displayed in the SQL _method tree:

sqxcrta

Create table as Select.

sqxslct

Select rows from table.

sqxjsl

Step loop join (Cartesian join).

sqxjm

Merge join execution.

sqxjndx

Index join execution.

sqxjhsh

Hash join execution.

sqxsort

Sort table or rows.

sqxsrc

Read rows from source.

sqxfil

Filter rows from table.

sqxsumg

Summary statistics (with GROUP BY).

sqxsumn

Summary statistics (not grouped).

sqxuniq

Distinct rows only.

sqxstj

STARJOIN

sqxxpgb

Parallel group-by

sqxxpjn

Parallel join with group-by. The SAS log displays the name of the parallel join method that was used.

sqxpll

Parallel join without group-by

Reading the Method Tree

A method tree is produced in your output if you specify the _method reset option for the SQL Planner. You read the SQL Planner method tree from bottom row to top row. The following example shows how to interpret the method tree by substituting the type of method that was used in each step.

PROC SQL ;
create table tbl1 as
 select *
  from path1.dansjunk1 a,
   path1.dansjunk2 b,
   path1.dansjunk3 c
  where a.i = b.i
   and a.i = c.i ;
quit ;

The following example method tree is printed:

SPDS_NOTE: SQL execution methods chosen are:
<0x00000001006BBD78> sqxslct
<0x00000001006BBBF8>     sqxjm
<0x00000001006BBB38>               sqxsort
<0x0000000100691058>                   sqxsrc
<0x0000000100667280>               sqxjm
<0x0000000100666C50>                       sqxsort
<0x0000000100690BD8>                           sqxsrc
<0x00000001006AE600>                       sqxsort
<0x0000000100694748>                           sqxsrc

You can review the sequence of methods that were invoked by reading the tree from bottom to top.

SPDS_NOTE: SQL execution methods chosen are:
<0x00000001006BBD78> step 9
<0x00000001006BBBF8>     step 8
<0x00000001006BBB38>               step 7
<0x0000000100691058>                    step 6
<0x0000000100667280>                 step 5
<0x0000000100666C50>                        step 4
<0x0000000100690BD8>                            step 3
<0x00000001006AE600>                       step 2
<0x0000000100694748>                              step 1

In step 1, sqxsrc reads rows from the source. In step 2, sqxsort sorts the table rows. Then in steps 3 and 4, more rows are read and sorted. In step 5, the tables are joined by sqxjm, and so on.

BUFFERSIZE=

The SPD Server query optimizer considers a number of join strategies. Some of the join strategies require memory buffers. In these cases, BUFFERSIZE= specifies the amount of memory that SPD Server should reserve for memory buffers.

For example, SPD Server SQL might consider a hash join when an index join is not possible. A hash join reconfigures the smaller table in memory as a hash table. SQL sequentially scans the larger table and performs a hash lookup row-by-row against the smaller table to form the result set. On a memory-rich system, consider increasing the BUFFERSIZE= option to increase the likelihood that a hash join is chosen. The default BUFFERSIZE= setting is 64 K. You can specify the amount of memory that you want SPD Server to use for hash joins.

Usage:

/* Increase buffersize from 64K   */
execute(reset 
  buffersize=n)
by sasspds ;

n

the maximum number of rows in the smaller table for a hash join that can use the inset size hash join optimization.

DETAILS=

Use the DETAILS= reset switch to provide additional information in the SAS log about the SQL joins that SPD Server made.

Usage:

execute(reset 
  details=("what_join$"|"why_join$"|"what_join$why_join$")
  by sasspds ;

DETAILS="what_join$"

adds additional information in the SAS log documenting the join plan that was selected.

DETAILS="why_join$"

adds additional information in the SAS log documenting why the join plan that was selected was chosen.

DETAILS="what_why_join$"

adds additional information in the SAS log documenting the join plan that was selected, and why the join plan that was selected was chosen.

EXEC / NOEXEC

You use the SPD Server SQL Planner EXEC / NOEXEC option to turn SPD Server SQL execution on or off.

Usage:

/* This explicit Pass-Through SQL */
/* prints the method tree without */
/* executing the SQL code. */

PROC SQL ;
connect to sasspds
  (dbq=domain
   server=<host-name>.<port-number>
   user='username') ;

execute (reset _method noexec)
 by sasspds ; /* turns SQL exec off */

execute (SQL statements)
 by sasspds ;

disconnect from sasspds ;
quit ;

HASHINSETSIZE

You use the SPD Server SQL planner HASHINSETSIZE reset option to influence when the hash join inset size optimization can be used. The hash join inset size optimization gathers join keys from the smaller join table, and then generates a query to the larger table. The query to the larger table only selects rows that can be joined to the smaller table. that only selects rows that can be joined to the smaller table. The join keys for the selected rows of the larger table are then hashed with the smaller table in order to perform the hash join.

Usage:

execute(reset 
  hashinsetsize=nby sasspds;

n

the maximum number of rows in the smaller table for a hash join that can use the inset size hash join optimization.

INDEXSELECTIVITY=

The INDEXSELECTIVITY= option enables you to tune the SQL join planner strategy for more efficient or robust index join methods. The INDEXSELECTIVITY= setting is a continuous value in the range 0–1 that acts as a minimum threshold value for the SPD Server cardinality ratio when selecting a join method. The SPD Server cardinality ratio is a heuristic that acts as a measure of the cardinality of the inner table index, relative to the frequency of index values as they occur in the outer table. Both INDEXSELECTIVITY= and the SPD Server cardinality ratio are continuous values between 0 and 1. SPD Server compares the calculated cardinality ratio for an SPD Server index join to the value that you specify in the INDEXSELECTIVITY= option. If the calculated cardinality ratio is greater than or equal to the value that is specified in the INDEXSELECTIVITY= option, SPD server chooses the index join method. The default setting for the INDEXSELECTIVITY= option is 0.7.

How does SPD Server calculate the cardinality ratio? The cardinality ratio of an indexed column is calculated as the number of unique values in the index column, divided by the number of rows in the outer table. As the value of the cardinality ratio approaches 0, which indicates low cardinality, the greater the number of duplicate values that exist in the rows of the outer table. As the value of the cardinality ratio approaches 1, which indicates high cardinality, the fewer the number of duplicate index values in the rows of the outer table. For example, a cardinality ratio of 1/1, or 1, represents a unique index value for every row in the outer table, a unique index. A cardinality ratio value of 1/2, or 0.5, represents a unique index value for every two rows in the outer table. A cardinality ratio value of 1/4, or 0.25, represents a unique index value for every four rows in the outer table. The default setting of INDEXSELECTIVITY= is 0.7, which represents a unique index value for every 1.43 rows in the outer table.

For example, consider an outer table that contains 100 rows that match join key values in the inner table, and a calculated SPD Server cardinality ratio of 0.7 (a unique index value per 1.43 rows in the outer table). The expected result set is 100*1.43, or 143 rows.

Higher cardinality and higher index cardinality ratios are associated with an efficient index join. Cardinality ratios near 1 result in more efficient processing during probes between the outer table rows and the inner table index, because each probe has fewer rows to retrieve. In turn, the work that the SPD Server index must do to find and retrieve the matching rows during the join operation is maximized, which results in an optimized index join.

You can use INDEXSELECTIVITY= to configure the index join to be more or less tightly constrained by the number of duplicate values in the join table rows. Increasing the value of INDEXSELECTIVITY= makes the cardinality criteria more selective by decreasing the allowable average number of rows per probe of the inner table. Setting INDEXSELECTIVITY= equal to 1.0 allows only a join with a unique index. Setting INDEXSELECTIVITY= to a value greater than 1.0 allows no index joins. Decreasing the value of INDEXSELECTIVITY= makes the cardinality criteria more flexible by increasing the allowable average number of rows per probe of the inner table. Setting INDEXSELECTIVITY= equal to 0.0 allows joins with any amount of cardinality.

Usage:

execute(reset indexselectivity=<0.0 ... 1.0>)
  by sasspds ;

INOBS

Use the INOBS option to specify the specific number of observations that you want to read from input tables.

Usage:

execute(reset inobs=<n>)
  by sasspds ;

The integer value <n> is the number of observations that you want to read.

JTECH PREF | JOINTECH_PREF

You use the SPD Server SQL Planner JTECH PREF | JOINTECH_PREF reset option to control how the SPD Server SQL Planner executes join statements. The option has three settings: 101, 102, and 103.

Usage:

execute(reset 
  jointech_pref=<seq|merge|hash|index>)
   by sasspds ;

where

JOINTECH_PREF=seq

SPD Server performs sequential loop joins. Sequential loop joins are brute force joins that match every row of the first table to every row of the second table.

JOINTECH_PREF=merge

SPD Server performs sort merge joins. Sort merge joins force a sort on all tables that are involved in the join.

JOINTECH_PREF=hash

SPD Server performs hash joins. Hash joins require SPD Server to create a memory table in order to perform the join. The size of the memory table is limited based on the available memory.

JOINTECH_PREF=index

SPD server will favor an index join, provided the join meets the index join criteria. The index join requires an index on the join column of one tables, and the index_selectivity requirement must be met (see the index_selectivity reset option).

MAXHASHJOIN

You use the SPD Server SQL planner MAXHASHJOIN reset option to control how many hash joins can be planned in a single statement.

Usage:

execute(reset  maxhashjoins=<n>)
by sasspds;

n

the number of hash joins that can be planned.

Note: Hash joins in SPD Server can be memory intensive. Increasing the number of hash joins is likely to increase the memory requirements for the query plan.

OUTOBS

Use the OUTOBS option to specify the specific number of observations that you want to create or print in your output.

Usage:

execute(reset outobs=<n>)
  by sasspds ;

The integer value <n>is the number of observations that you want to create or print.

OUTRSRTJNDX / NOOUTRSRTJNDX

Use the OUTRSRTJNDX / NOOUTRSRTJNDX option to configure the sort behavior for an SPD Server join index. OUTRSRTJNDX sorts the outer table for a join index by the join key. This setting is the default SPD Server setting. NOOUTRSRTJNDX does not sort the outer table for a join index.

Usage:

/* Disable outer table      */
/* sorting for a join index */
execute(reset nooutrsrtjndx)
  by sasspds ;


/* Enable outer table       */
/* sorting for a join index */
execute(reset outrsrtjndx)
  by sasspds ;

PRINTLOG / NOPRINTLOG

You use the PRINTLOG / NOPRINTLOG option of the SPD Server SQL Planner to turn on or off the printing of the SQL statement text to the SPD Server log.

Usage:

PROC SQL ;
connect to sasspds
  (dbq=domain
   server=<host-name>.<port-number>
   user='username') ;

/* turn SQL statement printing on */
execute (reset printlog)
by sasspds ;

/* all statements will be printed to SPD Server log */
execute (SQL statements)
by sasspds ;

/* turn SQL statement printing off */
execute (reset noprintlog)
by sasspds ;

disconnect from sasspds ;
quit ;

SASVIEW / NOSASVIEW

Use the SASVIEW / NOSASVIEW option to enable or disable SAS PROC SQL views that use an SPD Server LIBNAME. SAS PROC SQL views use a generic transport format to represent numeric values, which SPD Server converts to native numeric values. When extremely large or extremely small numeric values are conveyed in a SAS PROC SQL view to SPD Server, extreme values might not be as precise during the SPD Server numeric conversion.

Usage:

/* Disable SAS PROC SQL views     */
/* that use an SPD Server LIBNAME */
execute(reset nosasview)
  by sasspds ;

/* Enable SAS PROC SQL views that */
/* use an SPD Server LIBNAME      */
execute(reset sasview)
  by sasspds ;

If SAS PROC SQL views are disabled and SPD Server pass-through SQL uses a view that was created by PROC SQL, SPD Server rejects the PROC SQL statement and inserts the following error message in the SAS log:

SPDS_WARNING: SAS View and SASVIEW Reset Option equals No.
SPDS_ERROR: An error has occured.

If SAS PROC SQL views are enabled and SPD Server pass-through SQL uses a view that was created by PROC SQL, SPD Server prints the following note in the SAS log:

SPDS_NOTE: SPDS using SAS View in transport mode.

UNDO_POLICY=

Use the UNDO_POLICY option in SPD Server PROC SQL and RESET statements to configure SPD Server PROC SQL error recovery. When you update or insert rows in a table, you might receive an error message that states that the Update or Insert operation cannot be performed. The UNDO_POLICY option specifies how you want SPD Server to handle rows that were affected by INSERT or UPDATE statements that preceded a processing error.

Usage:

/* Do not undo any updates or inserts */
execute(reset undo_policy=none)
  by sasspds ;

/* Permit row inserts and updates to  */
/* be done up to the point of error   */
execute(reset undo_policy=required)
  by sasspds ;

UNDO_POLICY=NONE

the default setting for SPD Server. This setting does not undo any updates or inserts.

UNDO_POLICY=REQUIRED

undoes all row updates or inserts up to the point of error.

UNDO_POLICY=OPTIONAL

undoes any updates or inserts that it can undo reliably.

If the UNDO policy is not required, you get the following warning message for an insert into the table:

WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an
error is detected when processing this insert statement, that error
will not cause the entire statement to fail.

Additional SQL Reset Options

For more detailed information about the available SQL reset options for the SPD Server SQL Parallel Join, Parallel Group-By, STARJOIN, and Correlated Query facilities, see the following topics: