Variables to Enhance Performance

SPDSACWH=

The SPDSACWH= macro variable affects only Hadoop domains. Use the SPDSACWH= macro variable setting to override the default state of an undeclared HADOOPACCELWH= (WHERE processing optimization) setting. When SPD Server has a defined Hadoop domain and the SPD Server WHERE processing optimization parameter is not defined in the SPD Server spdsserv.parm file, the default WHERE processing optimization configuration reverts to NO. You can override an undeclared HADOOPACCELWH= setting by issuing a statement that contains the SPDSACWH= macro variable set to YES.

If your SPD Server administrator has enabled WHERE processing optimization via the HADOOPACCELWH= setting, you can use the SPDSACWH= macro variable to override and suppress the Hadoop WHERE processing optimization. To override and suppress a HADOOPACCELWH=YES setting, issue a statement that contains the SPDSACWH= macro variable set to NO.

Syntax

%let SPDSACWH=YES|NO

Default: NO

Arguments

YES

overrides the default state of an undeclared HADOOPACCELWH= WHERE processing optimization server parameter option. An undeclared HADOOPACCELWH= server parameter option defaults to NO. Submitting the macro variable SPDSACWH=YES overrides the default state of the undeclared server parameter option. If the HADOOPACCELWH= parameter option is set to NO (instead of undeclared), SPDSACWH= macro variable settings are ignored and WHERE processing optimization is not performed.

overrides (turns off) WHERE processing optimization if it was previously configured via a HADOOPACCELWH=YES statement in the SPD Server parameter file.

Table Option Equivalent

The table option equivalent to the SPDSACWH= macro variable is the ACCELWHERE= table option. Setting ACCELWHERE=YES is equivalent to setting SPDSACWH=YES. If a conflict occurs between settings specified via the ACCELWHERE= table option and the SPDSACWH= macro variable, the setting specified in the ACCELWHERE= table option takes precedence. For more detailed information about the ACCELWHERE= table option, see Options to Enhance Performance.

Example:

%let SPDSACWH=YES;

Note: Because SPD Server is able to operate in select Hadoop environments, you might want to determine whether a LIBNAME resides in a Hadoop domain. To determine whether the LIBNAME my_lib is in a Hadoop domain, submit the following LIBNAME LIST statement:

LIBNAME my_lib
                     LIST;

. If the queried LIBNAME is in a Hadoop domain, SPD Server returns HADOOP=YES to the LIBNAME LIST query.

SPDSAUNQ=

Use the SPDSAUNQ= macro variable setting to specify whether to cancel an append to a table if the table has a unique index and the append would violate the index uniqueness.

Syntax

SPDSAUNQ=YES|NO

Default: NO

Description:

Use SPDSAUNQ=YES macro variable to improve append performance to a table with unique indexes. If uniqueness is not maintained, the append is canceled and the table is returned to its state before the append. In such an instance, you can scrub the table to remove nonunique values and redo the append with the macro variable SPDSAUNQ= set to YES. You can also redo the append with the macro variable SPDSAUNQ= set to NO.

If SPDSAUNQ=NO, the SPD Server enforces uniqueness at the expense of appending unique indexes in observation order, one row at a time. If uniqueness is not maintained for any given row, that row is discarded from the append.

SPDSCLJX=

Use the SPDSCLJX= macro to affect the SAS SQL planner when joining a SAS table with an indexed SPD Server table.

Syntax

SPDSCLJX=YES|NO

Default: NO

Arguments

YES

The SAS SQL planner evaluates using the index join as a possible method when selecting a strategy for joining a Base SAS table with an SPD Server cluster table.

The SAS SQL planner disallows the index join as a possible method when selecting a strategy for joining a Base SAS table with an SPD Server cluster table.

Description

SPDSCLJX=YES can potentially improve processor performance by using an index join during heterogeneous joins between an SPD Server cluster table and Base SAS table. Index joins tend to be most beneficial when there are relatively few rows in the SAS table.

Note: The SPDSCLJX= option has no effect on implicit or explicit join queries to SPD Server that involve a cluster table.

SPDSHPRD=

The SPDSHRD= macro affects only Hadoop domains. Use the SPDSHPRD= macro variable to enable parallel reads by SPD Server when a WHERE clause is not specified. By default, SPD Server performs multi-threaded reads only when a WHERE clause is specified. The SPDSHPRD= macro variable configures SPD Server to perform parallel reads when no WHERE clause is specified.

Syntax

SPDSHPRD=YES|NO

Default: UNSPECIFIED (no supplied value). If unspecified, SPD Server performs parallel reads only when a WHERE clause is specified.

Arguments

YES

SPD Server performs multi-threaded reads whenever possible, regardless of whether a WHERE clause is invoked.

SPD Server performs multi-threaded reads only when a WHERE clause is invoked.

Description

SPDSHPRD= enables SPD Server users to perform parallel table reads when no WHERE clauses are used.

Corresponding Table Option

PARALLELREAD=YES|NO.

If both the PARALLELREAD= table option and the SPDSHPRD= macro variable settings have specified values, the PARALLELREAD= table option setting overrides the SPDSHPRD= macro variable setting.

Note: Because SPD Server is able to operate in select Hadoop environments, might want to determine whether a LIBNAME resides in a Hadoop domain. To determine whether the LIBNAME my_lib is in a Hadoop domain, submit the following LIBNAME LIST statement:LIBNAME my_lib LIST;. If the queried LIBNAME is in a Hadoop domain, SPD Server returns HADOOP=YES to the LIBNAME LIST query.

SPDSNETP=

Use the SPDSNETP= macro variable to size buffers in server memory for the network data packet.

Syntax

SPDSNETP=size-of-packet

Default: 32K

Corresponding Table Option: NETPACKSIZE=

Arguments

size-of-packet

is the size (integer) in bytes of the network packet.

Description

When sizing the buffer for data packet transfer between SPD Server and your SAS client machine, the packet must be greater than or equal in size to one table row. See NETPACKSIZE= for more information.

Example

Despite recent upgrades to your network connections, you are experiencing significant pauses when the SPD Server transfers data. You want to resize the data packet to send three rows at a time for a more continuous data flow.

Specify a buffer size in server memory that is three times the row size (6144 bytes.) Submit your SPDSNETP= macro variable statement at the top of your job.

%let SPDSNETP=18432;

SPDSSADD=

Use the SPDSSADD= macro variable to specify whether SPD Server appends tables by transferring a single row at a time synchronously, or by transferring multiple rows asynchronously (block row appends).

Syntax

SPDSSADD=YES|NO

Default: NO

Related Table Option: SYNCADD=

Arguments

YES

applies a single row at a time during an Append operation. This behavior imitates the Base SAS engine.

appends multiple rows at a time

Description

SPDSSADD=YES slows performance. Use this argument only if you require strict compatibility with Base SAS software when processing a table. For a complete discussion, refer to SYNCADD=.