Table Loading Techniques

The SAS data storage model adds rows to a data set one at a time. The SPD Server I/O engine buffers rows to be added from the SAS application and performs block adds using a highly efficient pipelined append protocol when communicating with the proxy.

Parallel Table Load Technique Using PROC APPEND

Parallel Table Load Technique Using SQL Pass-Through

Parallel Pass-Through Table Load and Data Subset

Parallel Pass-Through Table Copy

Parallel Table Load Technique Using PROC APPEND

To achieve significant improvements in building a table, create the empty table first, defining indexes on the desired columns. Then, use PROC APPEND to populate the table and indexes. The example below demonstrates this technique.

/* Create an empty SPD Server table with the same   */
/* columns and column attributes as the existing    */
/* SAS table.                                       */
data spdslib.cars;
set somelib.cars(obs=0);
run;

/* Create indexes for the empty table so the indexes */
/* are appended in parallel with the table appends.  */

PROC DATASETS lib=spdslib;
   modify cars;
   index create make;
   index create origin;
   index create mpg;
quit;

/* PROC APPEND SAS table Cars to SPD Server table  */
/* Cars. The append to the SPD Server table and    */
/* its indexes will occur in parallel.             */

PROC APPEND
   base=spdslib.cars
   data=somelib.cars;
run;

Parallel Table Load Technique Using SQL Pass-Through

If you are using SQL Pass-Through, consider using the LOAD TABLE command to perform the same operation. LOAD TABLE encapsulates the sequence of SAS DATA and PROC steps into an even more powerful technique for gaining maximum performance when loading a new table. The following example demonstrates the same table construction using LOAD TABLE and SQL Pass-Through:

/* Create a copy of the SPD Server table Cars and   */
/* its index from Example 1 to another SPD Server   */
/* table carload using Pass-Through LOAD command.   */
/* The table creation of the SPD Server table       */
/* carload and its indexes will occur in parallel.  */

execute(
load table carload with
   index make
     on (make),
   index origin
     on (origin),
   index mpg
     on (mpg)
   as select *
   from cars
) by sasspds;

Parallel Pass-Through Table Load and Data Subset

/* Create a subset of the SPD Server table Cars  */
/* from Example 1 to another SPD Server table    */
/* Fordcar using the Pass-Through LOAD command.  */
/* The table creation of the SPD Server table    */
/* Fordcar and its indexes occurs in parallel.   */

execute(
load table fordcar with
   index origin
     on (origin),
   index mpg
     on (mpg)
   as select *
   from cars
   where make="ford"
) by sasspds;

Parallel Pass-Through Table Copy

/* Create a copy of the SPD Server table Cars and  */
/* all its indexes from Example 1 to another Data  */
/* Server table Copycars using the Pass-Through    */
/* COPY command.  The table creation of the Data   */
/* Server table Copycars and its indexes will      */
/* occur in parallel.                              */

execute(
copy table copycars
  from cars
) by sasspds;