2

I have a large SAS dataset sorted by field 'A'. I'd like to do a query that references fields 'A' and 'B'. To speed up performance I created an index on 'B'. This results in an unhelpful message:

INFO: Index B not used.  Sorting into index order may help.

Of course sorting on B would help. But that's not the point. Indexes are for the case when you are already sorted on some other field.

In a similar query, SAS gives this message:

INFO: Use of index C for WHERE clause optimization canceled.

Any tips on getting SAS to use my indexes? In one case the query is taking 2 hours to run because SAS doesn't use the index.

Two Bit Gangster
  • 973
  • 2
  • 9
  • 14

2 Answers2

3

In case the query is not selective enough - taking most of source records to the result, the index use may not help performance, eventually can make things worse. That's probably why the optimizer desided not to use the index.

To force the use of index try using IDXNAME data set option (on both tables, probably). Refer to http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000414058.htm.

Without seeing the query and knowing some characteristics of data (at least record counts of input tables and expected size of the query result) it's hard to tell the optimal approach.

Anyway, for optimal performance, when joining tables, both tables need to be index similarly and all the join keys need to be part of the index.

vasja
  • 4,732
  • 13
  • 15
0

Can't answer a question like this without seeing the query you are trying to run. An index will only be useful if the SAS optimizer determines it will improve performance. Can you show a simple example of the code you want to run?

BellevueBob
  • 9,498
  • 5
  • 29
  • 56