Options That Affect Disk Space

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

ASYNCINDEX=YES|NO

Default: NO

SPDSIASY

Arguments

SPD Server can create multiple indexes for a table at the same time. To do this, it launches a single thread for each index created, and then processes the threads simultaneously. Although creating indexes in parallel is much faster, the default for this option is NO. The reason is because parallel creation requires additional sort work space that might not be available.

For a complete description of the benefits and tradeoffs of creating multiple indexes in parallel, see SPDSIASY= in the SAS Scalable Performance Data (SPD) Server 4.5: User's Guide.

Since the disk workspace required for parallel index creation is available, specify for SPD Server to create, in parallel, the X, Y, and COMP indexes for table A.

PROC DATASETS lib=mydatalib;
   modify a(asyncindex=yes);
   index create x;
   index create y;
   index create comp=(x y);
   quit;

Compresses SPD Server tables on disk.

COMPRESS=YES|NO

Default: NO

IOBLOCKSIZE=

SPDSDCMP

Arguments

When COMPRESS= is assigned YES, SPD Server compresses newly created tables by 'blocks' based on the algorithm specified. To control the amount of compression, use the table option IOBLOCKSIZE=. This option specifies the number of rows that you want to store in the block.

Specifies the size of an SPD Server table partition.

PARTSIZE=n

Default: 16 Megabytes

SPDSSIZE=

DATAPATH=

Arguments

Specifying PARTSIZE= forces the software to partition (split) SPD Server tables at the given size. The actual size is computed to accommodate the largest number of rows that will fit in the specified size of n Mbytes.

Use this option to improve performance of WHERE clause evaluation on non-indexed table columns and on SQL GROUP_BY processing. By splitting the data portion of a Scalable Platform Data Server table at fixed-sized intervals, the software can introduce a high degree of scalability for these operations. The reason: it can launch threads in parallel to perform the evaluation on different partitions of the table, without the threat of file access contention between the threads. There is, however, a price for the table splits: an increased number of files, which are required to store the rows of the table.

Ultimately, scalability limits using PARTSIZE= depend on how you structure DATAPATH=, a LIBNAME option discussed in the documentation on SPD Server LIBNAME Options . Specifically, the limits depend on how you configure and spread the DATAPATH= file systems across striped volumes. You should spread each individual volume's striping configuration across multiple disk controllers or SCSI channels in the disk storage array. The goal for the configuration is, at the hardware level, to maximize parallelism during data retrieval.

The PARTSIZE= specification is limited by MINPARTSIZE=, an SPD Server parameter maintained by the SPD Server administrator. MINPARTSIZE= ensures that an over-zealous SAS user does not create arbitrarily small partitions, thereby generating a large number of files. The default for MINPARTSIZE= is 16 Mbytes and probably should not be lowered much beyond this value.

Using PROC SQL, extract a set of rows from an existing table to create a non-indexed table with a partition size of 32 Mbytes in a SAS job:

PROC SQL;
create table SPDSCEN.HR80SPDS(partsize=32)
  as select
    state,
    age,
    sex,
    hour89,
    industry,
    occup
   from SPDSCEN.PRECS
   where hour89 > 40;
quit;