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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

[ Edited ]

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?


Accepted Solutions
Solution
‎02-20-2016 03:02 PM
Respected Advisor
Posts: 3,799

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

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

Capture.PNG

View solution in original post


All Replies
Super User
Posts: 7,832

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

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

Posted in reply to KurtBremser

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?

Solution
‎02-20-2016 03:02 PM
Respected Advisor
Posts: 3,799

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

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

Capture.PNG

Occasional Contributor
Posts: 11

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

Posted in reply to data_null__

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

Super User
Posts: 10,041

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

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

Occasional Contributor
Posts: 11

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

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.

Super User
Posts: 5,434

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

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.
Data never sleeps
Occasional Contributor
Posts: 11

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 488 views
  • 3 likes
  • 5 in conversation