Variables for WHERE Clause Evaluations

Use the SPDSTCNT= macro variable to specify the number of threads that you want to use during WHERE clause evaluations.

SPDSTCNT=n

Default: The value of MAXWHTHREADS is configured by SPD Server parameters.

Used in Conjunction with the SPD Server Parameter: MAXWHTHREADS

Corresponding Table Option: THREADNUM=

See THREADNUM= for a description and an explanation of how SPDSTCNT= interacts with the SPD Server parameter MAXWHTHREADS.

Use the SPDSEV1T= macro variable to indicate whether data returned from an SPD Server WHERE clause evaluations should be in strict row (observation) order.

The macro variables SPDSEV1T= and SPDSEV2T= work in conjunction with the SPD Server WHERE clause planner WHINIT.

The variables SPDSEV1T= and SPDSEV2T= are identical in purpose. You use them to specify the row order of data returned in WHERE-processing. Which variable the server exercises depends on the evaluation strategy selected by WHINIT. The SPDSEV1T= evaluation strategy is indexed. The SPDSEV2T= evaluation strategy is non-indexed. Avoid using these options unless you absolutely understand the SPD Server performance tradeoffs that depend on maintaining the order of data.

If compatibility with Base SAS software is important, set both SPDSEV1T= and SPDSEV2T= to 0. When both evaluation strategies are set to 0, SPD Server returns data in row order whether the SPDSEV1T= or the SPDSEV2T= strategy is selected.

When you use a SAS PROC to retrieve rows from a sorted table, some SAS PROCs can use the sort order information to optimize how to receive and process the rows. For example, if you use PROC SQL to perform table joins on a sorted table that uses WHERE predicates to filter table rows, then PROC SQL will use the sort order information to optimize the join strategy. If you use the default values of SPDSEV1T= and SPDSEV2T= in these instances, the SAS PROC receives the table rows in sorted order.

If the SAS PROC that you submit does not use the sorted order, the default values of SPDSEV1T= and SPDSEV2T= will restrict the use of parallel WHERE clauses, which can negatively impact performance. For example, PROC PRINT and most SAS DATA step code does not take advantage of sorted tables. If you know that the SAS PROC that you are submitting does not take advantage of a sorted table, you can change the setting for SPDSEV1T= or SPDSEV2T= to 2, in order to allow parallel WHERE evaluations that can improve performance. However, this should be done with care: a parallel WHERE evaluation does not guarantee that rows are returned to SAS in sorted order, and this can cause incorrect results for a SAS PROC that uses that information.

SPDSEV1T=0|1|2

Default: 1

Used in Conjunction with Indexed WHERE clause Evaluation Strategy

Arguments

If SPD Server must return many rows during WHERE clause processing, setting the variable to 0 will greatly slow performance. Use 0 only when row order is required. Use 2 only when you know row order is not important to the result.

Configure SPD Server to send back data in row order whenever WHINIT performs an EVAL1 evaluation.

%let SPDSEV1T=0; 

Use the SPDSEV2T= macro variable to specify whether the data returned from WHERE clause evaluations should be in strict row (observation) order.

The macro variables SPDSEV1T= and SPDSEV2T= work in conjunction with the SPD Server WHERE clause planner WHINIT.

The variables SPDSEV1T= and SPDSEV2T= are identical in purpose. You use them to specify the row order of data returned in WHERE-processing. Which variable the server exercises depends on the evaluation strategy selected by WHINIT. The SPDSEV1T= evaluation strategy is indexed. The SPDSEV2T= evaluation strategy is non-indexed. Avoid using these options unless you absolutely understand the SPD Server performance tradeoffs that depend on maintaining the order of data.

If compatibility with Base SAS software is important, set both SPDSEV1T= and SPDSEV2T= to 0. When both evaluation strategies are set to 0, SPD Server returns data in row order whether the SPDSEV1T= or the SPDSEV2T= strategy is selected.

When you use a SAS PROC to retrieve rows from a sorted table, some SAS PROCs can use the sort order information to optimize how to receive and process the rows. For example, if you use PROC SQL to perform table joins on a sorted table that uses WHERE predicates to filter table rows, then PROC SQL will use the sort order information to optimize the join strategy. If you use the default values of SPDSEV1T= and SPDSEV2T= in these instances, the SAS PROC receives the table rows in sorted order.

If the SAS PROC that you submit does not use the sorted order, the default values of SPDSEV1T= and SPDSEV2T= will restrict the use of parallel WHERE clauses, which can negatively impact performance. For example, PROC PRINT and most SAS DATA step code does not take advantage of sorted tables. If you know that the SAS PROC that you are submitting does not take advantage of a sorted table, you can change the setting for SPDSEV1T= or SPDSEV2T= to 2, in order to allow parallel WHERE evaluations that can improve performance. However, this should be done with care: a parallel WHERE evaluation does not guarantee that rows are returned to SAS in sorted order, and this can cause incorrect results for a SAS PROC that uses that information.

SPDSEV2T=0|1|2

Default: 1

Used in Conjunction with Non-Indexed WHERE clause Evaluation Strategy

Arguments

If SPD Server must return many rows during WHERE clause processing, setting the variable to 0 will greatly slow performance. Use 0 only when row order is required. Use 2 only when you know row order is not important to the result.

Configure SPD Server to send back data in row order whenever WHINIT performs an EVAL2 evaluation.

%let SPDSEV2T=0;  

Use the SPDSWDEB= macro variable to specify whether the WHERE clause planner WHINIT, when evaluating a WHERE expression, should display a summary of the execution plan.

SPDSWDEB=YES|NO

Default: NO

Arguments

Use the SPDSIRAT= macro variables to specify whether to perform segment candidate pre-evaluation when performing WHERE clause processing with hybrid indexes.

SPDSIRAT=0..100

Default: MAXSEGRATIO server parameter

Description:

When using hybrid indexes, WHERE-based queries pre-evaluate segments. The segments are scanned for candidates that match one or more predicates in the WHERE clause. The candidate segments that were identified during the pre-evaluation are queried in subsequent logic to evaluate the WHERE clause. Eliminating the non-candidate segments from the WHERE clause evaluation generally results in substantial performance gains.

Some queries can benefit by limiting the pre-evaluation phase. SPD Server imposes the limit based on a ratio: the number of segments that contain candidates compared to the total number of segments in the table. The reason for this is simple. If the predicate has candidates in a high percentage of the segments, the pre-evaluation work is largely wasted.

The ratio formed by dividing the number of segments that containing candidates by the number of total segments is compared to a cutoff point. If the segment ratio is greater than the value assigned to the cutoff point, the extra processing required to perform pre-evaluation outweighs any potential process savings that might be gained through the predicate pre-evaluation. SPD Server calculates the ratio for a given predicate and compares the ratio to the SPDSIRAT= value, which acts as the cutoff point. If the calculated ratio is less than or equal to the SPDSIRAT= value, pre-evaluation is performed. If the calculated ratio is greater than the SPDSIRAT= value, pre-evaluation is skipped and every segment is a candidate for the WHERE clause.

Use the global SPD Server parameter, MAXSEGRATIO to set the default cutoff value. The default MAXSEGRATIO should provide good performance. Certain specific query situations might be justification for modifying your SPDSIRAT= value. When you modify your SPDSIRAT= value, it overrides the default value established by MAXSEGRATIO.

Example:

Configure SPD Server to perform a pre-evaluation phase for WHERE clause processing with hybrid indexes if the candidates are in 65% or less of the segments.

%let SPDSIRAT=65; 

Use the SPDSNIDX= macro variable to specify whether to use the table's indexes when processing WHERE clauses. SPDSNIDX= can also be used to disable index use for BY- order determination.

SPDSNIDX=YES|NO

Default: NO

Corresponding Table Option: NOINDEX=

Arguments

Description:

Set SPDSNIDX=YES to test the effect of indexes on performance or for specific processing. Do not use YES routinely for normal processing.

Example:

Assume you are processing data from SPORT.MAILLIST. There is an index for the SEX column, and you should test it to determine whether the index will improve performance when you use PROC PRINT processing on SPORT.MAILLIST.

You should configure SPD Server not to use the index:

data sport.maillist;
  input
    name $ 1-20
    address $ 21-57
    phoneno $ 58-69
    sex $71;

datalines;

Douglas, Mike       3256 Main St., Cary, NC 27511        919-444-5555 M
Walters, Ann Marie  256 Evans Dr., Durham, NC 27707      919-324-6786 F
Turner, Julia       709 Cedar Rd., Cary, NC 27513        919-555-9045 F
Cashwell, Jack      567 Scott Ln., Chapel Hill, NC 27514 919-533-3845 M
Clark, John         9 Church St.,  Durham, NC 27705      919-324-0390 M
;

PROC DATASETS lib=sport nolist;
modify maillist;
index create sex;
quit;

/*Turn on the macro variable SPDSWDEB */
/* to show that the index is not used */
/* during the table processing.       */

%let spdswdeb=YES;

%let spdsnidx=YES;

title "All Females from Current Mailing List";
PROC PRINT data=sport.maillist;
where sex="F";
run;

%let spdsnidx=NO;

Use the SPDSWCST= macro variable to specify whether to use dynamic WHERE clause costing.

SPDSWCST=YES|NO

Default: NO

Corresponding Server Parameter Option: [NO]WHERECOSTING

Turns WHERE-costing on or off for an entire server.

Description:

Set SPDSWCST=YES to use dynamic WHERE clause costing. Disabling SPDSWCST= defaults SPD Server to using WHERE-costing with WHINIT.

Example:

%let SPDSWCST=YES;

SPDSWSEQ=YES|NO

Default: NO

Description:

Set the SPDSWSEQ= macro variable to YES. When set to YES, the SPDSWSEQ= macro variable overrides WHERE clause costing and forces a global EVAL3 or EVAL4 strategy.

Example:

%let SPDSWSEQ=YES;