SPDSIASY Macro Variable

Specifies whether to create indexes in parallel when creating multiple indexes on an SPD Server table.

Valid in:	SPD Server
Default:	NO
Restriction:	Assignments for macro variables with character input (for example, YES \| NO \| BINARY arguments) must be entered in uppercase (capitalized).
Interaction:	Corresponding table option is ASYNCINDEX=.

Syntax

Required Arguments

Details

Example

Syntax

SPDSIASY=YES | NO

Required Arguments

YES

creates the indexes in parallel.

NO

creates one index at a time.

Details

You use the macro variable SPDSIASY to choose between parallel and sequential index creation on the server tables with more than one index. One advantage of creating multiple indexes in parallel is speed. The speed enhancements that can be achieved with parallel indexes are not free. Parallel indexes require significantly more disk space for working storage. The default server setting for the SPDSIASY macro variable is set to NO, in order to avoid exhausting the available work storage space.

However, if you have adequate disk space to support parallel sorts, it is strongly recommended that you override the default SPDSIASY=NO setting and assign SPDSIASY=YES. You can substantially increase performance—indexes that otherwise take hours to build complete much faster.

How many indexes should you create in parallel? The answer depends on several factors, such as the number of CPUs in the SMP configuration and available work storage space needed for index key sorting.

When managing disk space on your server, remember that grouping INDEX CREATE statements can minimize the number of table scans that the server performs, but it also affects disk space consumption. There is an inverse relationship between the table scan frequency and disk space requirements. A minimal number of table scans requires more auxiliary disk space. A maximum number of table scans requires less auxiliary disk space.

Example

You perform batch processing from midnight to 6:00 a.m. All of your processing must be completed before start of the next work day. One frequently repeated batch job creates large indexes on a table, and usually takes several hours to complete. Configure the server to create indexes in parallel to reduce the processing time.

%let SPDSIASY=YES;
proc datasets lib=spds;
   modify a;
   index create x;
   index create y;
   modify a;
   index create comp=(x y) comp2=(y x);
   quit;

In the example above, the X and Y indexes will be created in parallel. After creating X and Y indexes, the server creates the COMP and COMP2 indexes in parallel. In this example, two table scans are required: one table scan for the X and Y indexes, and a second table scan for the COMP and COMP2 indexes.

SPDSIASY Macro Variable

Syntax

Required Arguments

YES

NO

Details

Example

See Also