SPDSEV2T Macro Variable

Specifies whether the data returned from WHERE clause evaluations should be in strict row order.

Valid in: SPD Server
Default: 1
Interactions: Use in conjunction with the indexed WHERE clause evaluation strategy.
This macro variable works in conjunction with the SPD Server WHERE clause planner WHINIT.
Note: The SPDSEV2T evaluation strategy is non-indexed.
Syntax
Required Arguments
Details
See Also

Syntax

SPDSEV2T=0 | 1 | 2

Required Arguments

0

returns data in row order.

Tip
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.

1

might not return the data in row order. The server can override as needed to force a 0 setting if the table is sorted using PROC SORT.

2

always forces parallel evaluation regardless of sorted order. Might not return data in row order.

Tip
Use 2 only when you know that row order is not important to the result.

Details

Use the SPDSEV2T macro variable to specify whether the data returned from WHERE clause evaluations should be in strict row order.
The macro variables SPDSEV1T and SPDSEV2T work in conjunction with the 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 completely understand the 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, the server returns data in row order whether the SPDSEV1T or the SPDSEV2T strategy is selected.
You use a SAS procedure to retrieve rows from a sorted table. Some SAS procedures can use the sort order information to optimize how to receive and process the rows. For example, you use PROC SQL to perform table joins on a sorted table that uses WHERE predicates to filter table rows. PROC SQL uses the sort order information to optimize the join strategy. If you use the default values of SPDSEV1T and SPDSEV2T in these instances, the SAS procedure receives the table rows in sorted order.
If the SAS procedure 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 procedure that you are submitting does not take advantage of a sorted table, you can change the setting for SPDSEV1T or SPDSEV2T to 2. This change allows 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 procedure that uses that information.
Note: The SPDSEV1T and SPDSEV2T usage that is discussed here does not apply to SQL statements that are executed via the server SQL explicit pass-through facility.

See Also

Concepts:
WHERE Clause Planner
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 8, 2017