MAXSEGRATIO= System Option

Controls what percentage of index segments to identify as candidate segments before processing the WHERE expression. This occurs when evaluating a WHERE expression that contains indexed variables.

Valid in:	Configuration file, SAS invocation, OPTIONS statement, System Options window
Category:	System administration: Performance
Default:	75
Engine:	SPD Engine only

Syntax

Required Argument

Details

Examples

Example 1: Identifying Index Segments

Example 2: Applying the WHERE Expression to All Segments

Example 3: Not Stopping Until All Index Segments Are Evaluated

Syntax

MAXSEGRATIO=n

Required Argument

n

specifies an upper limit for the percentage of index segments that the SPD Engine identifies as containing the value referenced in the WHERE expression. The default is 75, which specifies for the SPD Engine to do the following:

use the index to identify segments that contain the particular WHERE expression value
stop identifying candidate segments when more than 75% of all segments are found to contain the value

The range of valid values is integers between 0 and 100. If n=0, the SPD Engine does not try to identify candidate segments, but instead applies the WHERE expression to all segments. If n=100, the SPD Engine checks 100% of the segments to identify candidate segments, and then applies the WHERE expression only to those candidate segments.

Details

For WHERE queries on indexed variables, the SPD Engine determines the number of index segments that contain one or more variable values that match one or more of the conditions in the WHERE expression. Often, a substantial performance gain can be realized if the WHERE expression is applied only to the segments that contain observations satisfying the WHERE expression.

The SPD Engine uses the value of MAXSEGRATIO= to determine at what point the cost of applying the WHERE expression to every segment would be less than the cost of continuing to identify candidate segments. When the calculated ratio exceeds the ratio specified in MAXSEGRATIO=, the SPD Engine stops identifying candidate segments and applies the WHERE expression to all segments.

Note: For a few tables, 75% might not be the optimal setting. To determine a better setting, run a performance benchmark, adjust the percentage, and rerun the performance benchmark. Comparing results shows you how the specific data population that you are querying responds to shifting the index-segment ratio.

Examples

Example 1: Identifying Index Segments

The following example causes the SPD Engine to begin identifying index segments that might satisfy the WHERE expression until the percentage of identified segments, compared to the total number of segments, exceeds 65. If the percentage exceeds 65, the SPD Engine stops identifying candidate segments and applies the WHERE expression to all segments:

 options maxsegratio=65;

Example 2: Applying the WHERE Expression to All Segments

The following example causes the SPD Engine to apply the WHERE expression to all segments without first identifying any candidate segments:

options maxsegratio=0;

Example 3: Not Stopping Until All Index Segments Are Evaluated

The following example causes the SPD Engine to begin identifying index segments and to not stop until it has evaluated all segments. Then, the WHERE expression is applied to all candidate segments that were identified:

options maxsegratio=100;