Examples of Creating SPD Server Indexes :: SAS(R) Scalable Performance Data Server 5.1: User's Guide, Second Edition

Example 1: Creating SPD Server Indexes from a DATA Step

The following code creates SPD Server table X. Next, the code creates a simple SPD Server index X on column X, and a composite SPD Server index Y on columns (A B).

    data foo.x(
      index=(x y=(a b)));
      x=1;
      a="Doe";
      b=20;
    run;

Example 2: Creating SPD Server Indexes from PROC DATASETS

The following code creates the same simple and composite SPD Server indexes that were created in Example 1. This code assumes that the same DATA step was executed, which did not include the creation of an index.

    PROC DATASETS lib=foo;
      modify x;
      index create x;
      index create y=(a b);
    quit;

Example 3: Creating SPD Server Indexes By Using SQL

The following code creates the same simple and composite SPD Server indexes that were created in Example 1. This code assumes that the same DATA step was executed, which did not include the creation of an index.

    PROC SQL;
    create index x
        on foo.x (x);
    create index y
        on foo.x (a,b);
    quit;

Example 4: Creating SPD Server Indexes Using Pass-Through SQL

The following code creates the same simple and composite SPD Server indexes that were created in Example 1. This code assumes that the same DATA step was executed, which did not include the creation of an index.

    PROC SQL;
    connect to sasspds (
       dbq="path1"
       server=host.port
       user='anonymous');

      execute(create index x on x (x))
      by sasspds;

      execute(create index y on x (a,b))
      by sasspds;
    quit;

Example 5: Using VERBOSE= to See Index Information

Sometimes you want to see information about indexes that are associated with a particular table. The table option VERBOSE= provides details about all indexes that are associated with an SPD Server table. For example, suppose you use the code from Example 2,and then use the following expression:

    PROC CONTENTS
      data=sports.expraqs
      (verbose=yes);
    run;

The result will be the following output:

       Alphabetic List of Index Info:
Bitmap Index (No Global Index):         GRIPSIZE
KeyValue (Min):                 4.250000
KeyValue (Max):                 5.000000
# of Discrete values:           3

Example 6: Using PROC SORT with SPD Server

If you use PROC SORT with SPD Server, your table is sorted. But suppose that you submit a PROC SORT statement to sort a table that was not previously indexed, or that was sorted on the table's BY column.

In this scenario, PROC SORT takes advantage the fact that SPD Server sorts implicitly and asserts BY clause ordering to the SPD Server. This process performs the sort on the SPD Server machine, but there is still significant I/O between the client node and the SPD Server machine. The sorted data makes a round trip from the server machine to the client machine, and then back again. Fortunately, the SQL pass-through facility in SPD Server offers an extension to SQL to allow a table copy and sort operation, all on the server machine.

To avoid inefficiency, eliminate PROC SORT statements from your SAS jobs where possible. Instead, make SAS procedures and DATA steps that require BY clause processing use SPD Server's implicit sorts.

Example 7: Using the Implicit SPD Server BY Clause Sort

   /* The following DATA step performs a server sort on the   */
   /* table column PRICE. There is no prior index for PRICE.  */

      data _null_;
      set sport.expraqs;
        by price;
           if (string='nat') then do;
           put '*' @@;
           price = price - 30.00;
        end;
      put raqname @30 price;

Example 8: Using PROC SORT

      /* The following PROC SORT performs a server sort on the  */
      /* table column MODEL. There is no prior index for MODEL. */

        PROC SORT
          data=inventory.old_autos
          out=inventory.old_autos_by_model;
          by model;
        run;