topic Re: In which scenario would you not use an index even though using an index would increase efficienc in SAS Programming

In which scenario would you not use an index even though using an index would increase efficiency?

krzy32 — Fri, 19 Feb 2016 07:09:37 GMT

SAS 9.4

I am learning Advanced SAS currently. The idxwhere=no options can be used to specify not to use an index. In what possible scenario would you tell SAS not to use indexes ? SAS predicts whether to use an index or not. If the program fulfils criteria for not using indexes, it's obvious even SAS knows those scenarios. Why would you decrease efficiency by telling SAS not to use an index?

Re: In which scenario would you not use an index even though using an index would increase efficienc

Kurt_Bremser — Fri, 19 Feb 2016 07:16:17 GMT

Remember that computers are dumb. This option comes into play when you prove to be more intelligent than the SAS optimization algorithm.

Re: In which scenario would you not use an index even though using an index would increase efficienc

krzy32 — Fri, 19 Feb 2016 07:40:16 GMT

Yes but I cannot imagine any scenario in which I would need to explicitly tell SAS not to use indexes even though I risk decreasing efficiency but get same results as when index was used. Can you help me or have you come across any such scenario?

Re: In which scenario would you not use an index even though using an index would increase efficienc

Ksharp — Fri, 19 Feb 2016 07:46:55 GMT

When you get most part of a table (like 70% 80%),not using index is faster than using index.

Re: In which scenario would you not use an index even though using an index would increase efficienc

krzy32 — Fri, 19 Feb 2016 07:50:14 GMT

Agreed but SAS has an inbuilt algorithm that estimates output dataset. In the scenario you specified, had we not mentioned any index option, SAS would not have used indexing as about 70%-80% of dataset is being used.

Re: In which scenario would you not use an index even though using an index would increase efficienc

LinusH — Fri, 19 Feb 2016 11:50:43 GMT

Yes there is an algorithm. But that can't foresee everything. So I guess that there is no straight answer. I'm thinking that in a real life example find the use of index inefficient and thenot turn that off explicitly for that query.
One situation I can imagine is the the index is heavily fragmented so that even if just 10% rows is returned, let's say that 50% of the data pages is read.

Re: In which scenario would you not use an index even though using an index would increase efficienc

data_null__ — Fri, 19 Feb 2016 14:33:48 GMT

I don't know if this is a good example but I shows one scenario where turning off the index changes what you get back. Notice that using the index the values are returned in INDEX order while not using it they are returned in OBS order.

41         options msglevel=i;
42         data byage;
43            set class;
44            where age in(12 15);
INFO: Index Age selected for WHERE clause optimization.
45            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
      WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds
      

46         proc print;
47            run;

NOTE: There were 9 observations read from the data set WORK.BYAGE.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

48         data byage;
49            set class(idxwhere=no);
50            where age in(12 15);
INFO: Data set option (IDXWHERE=NO) forced a sequential pass of the data rather than use of an index for where-clause processing.
51            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
      WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Re: In which scenario would you not use an index even though using an index would increase efficienc

krzy32 — Fri, 19 Feb 2016 14:41:12 GMT

Yes, even i'm trying to imagine a scenario. Maybe what you say could happen.

Re: In which scenario would you not use an index even though using an index would increase efficienc

krzy32 — Sat, 20 Feb 2016 20:04:40 GMT

Thank you for your answer. Can you please post the log with the fullstimer option?