Important SPD Server SQL Planner Options

_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 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 when the _method reset option is specified for the SQL Planner. The SQL Planner method tree is read from bottom row to top row. Below is an example that 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 ;

Here is the example Method Tree that was 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

Reading from bottom to top, you can review the sequence of methods that were invoked.

SPDS_NOTE: SQL execution methods chosen are:
<0x00000001006BBD78> step-9
<0x00000001006BBBF8>     step-8
<0x00000001006BBB38>               step7
<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 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 row-by-row performs a hash lookup against the small 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 64K. 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=1048576)
  by sasspds ;

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 ;

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 between 0 and 1 that acts as a minimum threshold value for the SPD Server duplicity ratio when selecting a join method. The SPD Server duplicity 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 duplicity ratio are continuous values between 0 and 1. SPD Server compares the calculated duplicity ratio for an SPD Server index join to the value that is specified in the INDEXSELECTIVITY= option. If the calculated duplicity ratio is greater than or equal to the value that is specified in the INDEXSELECTIVITY= option, the index join method is chosen. The default setting for the INDEXSELECTIVITY= option is 0.7.

How is the SPD Server duplicity ratio calculated? The duplicity 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 duplicity ratio approaches 0, indicating low cardinality, the greater the number of duplicate values that exist in the rows of the outer table. As the value of the duplicity ratio approaches 1, indicating high cardinality, the fewer the number of duplicate index values in the rows of the outer table. For example, a duplicity ratio of 1/1, or 1, represents a unique index value for every row in the outer table, a unique index. A duplicity ratio value of 1/2, or 0.5, represents a unique index value for every two rows in the outer table. A duplicity 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, representing 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 duplicity ratio of 0.7 (a unique index value per 1.43 rows in the outer table,) the expected result set would be 100*1.43, or 143 rows.

From an efficiency perspective, higher cardinality and index duplicity ratios are considered better for an index join. Duplicity ratios near 1 mean more efficient processing during probes between the outer table rows and the inner table index, because each probe has fewer rows to retrieve. This in turn minimizes the work the SPD Server index must do to find and retrieve the matching rows during the join operation, resulting 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 duplicity criteria more selective by decreasing the allowable average number of rows per probe of the inner table. Setting INDEXSELECTIVITY= equal to 1.0 allows a join with a unique index only. Setting INDEXSELECTIVITY= to a value greater than 1.0 allows no index joins. Decreasing the value of INDEXSELECTIVITY= makes the duplicity criteria more forgiving 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 duplicity.

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 ;

where the integer value <n> is the desired number of observations.

MAGIC

You use the SPD Server SQL Planner MAGIC reset option that controls how the SPD Server SQL planner executes join statements. The Magic option has three settings, 101, 102, and 103.

Usage:

execute(reset magic=<101/102/103>)
  by sasspds ;

MAGIC=101

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

MAGIC=102

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

MAGIC=103

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 memory available.

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 ;

where the integer value <n> is the desired number of observations.

OUTRSRTJNDX/NOOUTRSRTJNDX

Use the OUTRSRTJNDX/NOOUTRSRTJNDX option to configure sort behavior for an SPD Server join index. OUTRSRTJNDX sorts the outer table for a join index by the join key. This 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 the printing of the SQL statement text to the SPD Server log on or off.

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, some precision might be lost in extreme values 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.

SQLHIMEM

Use the SQLHIMEM option to dynamically allocate or deallocate large memory blocks to and from the SPD Server SQL proxy address space. Providing the SPD Server SQL proxy address space with large memory blocks enhances processing during memory-intensive operations such as large table sorts and joins. By deallocating the large memory blocks after the memory overhead is no longer needed, memory resources for the SPD Server process resident set size, the SPD Server SQL proxy address space, and the SPD Server swap space are freed, which allows the resources to be used elsewhere by the system. If the SQLHIMEM option is not specified, system memory calls malloc() and free() are used to allocate and deallocate memory from the process heap. The trade-off with using SQLHIMEM instead of malloc() and free() is the need to use SQLHIMEM to allocate and deallocate memory for each SQL operation for a given user, as opposed to acquiring the SQL proxy address space once via malloc() and retaining the use of the memory resources.

Usage:

execute(reset SQLHIMEM) by sasspds;

By default, SPD Server does not use SQLHIMEM.

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

is the default setting for SPD Server. It 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

More detailed information about the available SQL reset options for the SPD Server SQL Parallel Join, Parallel Group-By, STARJOIN, and Correlated Query facilities can be found in this document as follows: