Options for WHERE Clause Evaluations

MINMAXVARLIST=

Creates a list that documents the minimum and maximum values of specified variables. SPD Server WHERE clause evaluations use MINMAXVARLIST= lists to include or eliminate member tables in an SPD Server dynamic cluster table from SQL evaluation scans..

Syntax

MINMAXVARLIST=(varname1 varname2 ... varnameN)

Arguments

varname1 varname2 ... varname N

are SPD Server table variable names.

Description

The primary purpose of the MINMAXVARLIST= table option is for use with SPD Server where specific members in the dynamic cluster contain a set or range of values, such as sales data for a given month. (For more information about dynamic cluster tables, see SAS Scalable Performance Data (SPD) Server Dynamic Cluster Tables.) When an SPD Server SQL subsetting WHERE clause specifies specific months from a range of sales data, the WHERE planner checks the MIN and MAX list. Based on the MIN and MAX list information, the SPD Server WHERE planner includes or eliminates member tables in the dynamic cluster for evaluation.

MINMAXVARLIST= uses the list of columns you submit to build the list. The MINMAXVARLIST= list contains only the minimum and maximum values for each column. The WHERE clause planner uses the index to filter SQL predicates quickly, and to include or eliminate member tables belonging to the cluster table from the evaluation.

Although the MINMAXVARLIST= table option is primarily intended for use with dynamic clusters, it also works on standard SPD Server tables. MINMAXVARLIST= can help reduce the need to create many indexes on a table, which can save valuable resources and space.

Example

%let domain=path3 ;
%let host=kaboom ;
%let port=5201 ;

LIBNAME &domain sasspds "&domain"
   server=&host..&port
   user='anonymous' ;

/* Create three tables called */
/* xy1, xy2, and xy3.         */

data &domain..xy1(minmaxvarlist=(x y));
  do x = 1 to 10;
  do y = 1 to 3;
  output;
  end;
end;
run;

data &domain..xy2(minmaxvarlist=(x y));
  do x = 11 to 20;
  do y = 4 to 6 ;
  output;
  end;
end;
run;

data &domain..xy3(minmaxvarlist=(x y));
  do x = 21 to 30;
  do y = 7 to 9 ;
  output;
  end;
end;
run;


/* Create a dynamic cluster table */
/* called cluster_table out of    */
/* new tables xy1, xy2, and xy3   */

PROC SPDO library=&domain ;
   cluster create cluster_table
      mem=xy1
      mem=xy2
      mem=xy3
      maxslot=10;
quit;


/* Enable WHERE evaluation to see  */
/* how the SQL planner selects     */
/* members from the cluster. Each  */
/* member is evaluated using the   */
/* min-max list.                   */

%let SPDSWDEB=YES;


/* The first member has true rows  */

PROC PRINT data=&domain..cluster_table ;
   where x eq 3
   and y eq 3;
run;


/* Examine the other tables */

PROC PRINT data=&domain..cluster_table ;
   where x eq 3
   and y eq 3 ;
run;

PROC PRINT data=&domain..cluster_table ;
   where x eq 3
   and y eq 3;
run;

PROC PRINT data=&domain..cluster_table ;
   where x between 1 and 10
   and y eq 3;
run;

PROC PRINT data=&domain..cluster_table ;
   where x between 11 and 30
   and y eq 8 ;
run;


/* Delete the dynamic cluster table. */

PROC DATASETS lib=&domain nolist;
   delete cluster_table ;
quit ;

THREADNUM=

Specifies the number of threads to be used for WHERE clause evaluations.

Syntax

THREADNUM=n

Default: THREADNUM= is set equal to the value of the MAXWHTHREADS server parameter.

Used in Conjunction with SPD Server Parameter

MAXWHTHREADS

Corresponding Macro Variable

SPDSTCNT=

Arguments

is the number of threads.

Description

THREADNUM= allows you to specify the thread count the SPD Server should use when performing a parallel WHERE clause evaluation.

Use this option to explore scalability for WHERE clause and GROUP_BY evaluations in non-production jobs. If you use this option for production jobs, you are likely to lower the level of parallelism that is applied to those clause evaluations.

THREADNUM= works in conjunction with MAXWHTHREADS, a configurable system parameter. MAXWHTHREADS imposes an upper limit on the consumption of system resources. The default value of MAXWHTHREADS is dependent on your operating system. Your SPD Server administrator can change the default value for MAXWHTHREADS.

If you do not use THREADNUM=, the software provides a default thread number, up to the value of MAXWHTHREADS as required. If you use THREADNUM=, the value that you specify is also constrained by the MAXWHTHREADS value.

The THREADNUM= value applies both to parallel table scans (EVAL2 strategy), parallel indexed evaluations (EVAL1 strategy), parallel BY-clause processing, and parallel GROUP_BY evaluations. See the SAS Scalable Performance Data (SPD) Server 4.5: User's Guide for more information about Optimizing WHERE Clauses.

Example

The SPD Server administrator set MAXWHTHREADS=128 in the SAS Scalable Performance Data (SPD) Server's parameter file. Explore the effects of parallelism on a given query by using the following SAS macro:

%macro dotest(maxthr);
%do nthr=1 %to &maxthr
   data _null_;
     set SPDSCEN.PRECS(threadnum=&nthr);
     WHERE
       occup='022'
       and state in('37','03','06','36');
   run;
%mend dotest;

WHERENOINDEX=

Specifies a list of indexes to exclude when making WHERE clause evaluations.

Syntax

WHERENOINDEX=(name1 name2...)

Arguments

(name1 name2...)

a list of index names that you want to exclude from the WHERE planner.

Example

We have a table PRECS with indexes defined as follows:

PROC DATASETS lib=spdscen;
modify precs(bitindex=(hour89));
index create
  stser=(state serialno)
  occind=(occup industry)
  hour89;
quit;

When evaluating the next query, we want the SPD Server to exclude from consideration indexes for both the STATE and HOUR89 columns.

In this case, we know that the AND combination of the predicates for the OCCUP and INDUSTRY columns will produce a very small yield. Few rows satisfy the respective predicates. To avoid the extra index I/O (machine time) that the query requires for a full-indexed evaluation, use the following SAS code:

PROC SQL;
create table hr80spds
  as select
    state,
    age,
    sex,
    hour89,
    industry,
    occup
  from spdscen.precs(wherenoindex=(stser hour89))
    where occup='022'
    and state in('37','03','06','36')
    and industry='012'
    and hour89 > 40;
quit;

Note: Specify index names in the WHERENOINDEX list, not the column names. The example excludes both the composite index for the STATE column STSER and the simple index HOUR89 from consideration by the WHINIT WHERE planner.