Date:         Tue, 15 Feb 2011 10:17:24 -0500
Reply-To:     Michael Raithel <michaelraithel@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael Raithel <michaelraithel@WESTAT.COM>
Subject:      Re: why query doesn't use index?
In-Reply-To:  <201102140040.p1DBlIXB025624@waikiki.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Dear SAS-l-ers,

Art posted the following:

> Max,
>
> Now that I know which data you are working with, on my computer the log
> indicates that increasing the bufno to 8 might allow the index to be
> used on
> the 2nd set of statements.  I tried that and, indeed, the index was
> used.
>

Art, I am playing catch-up and, having mustered the time to take a quick look at this thread, relish providing a quick insight:-)

The manipulation of BUFNO shouldn't affect whether or not SAS uses an index.

However, speaking of buffers, Max may want to explore increasing the number of available Index buffers via the IBUFNO option.  Increasing the value of IBUFNO will allow more index pages to be kept in memory at a time, so that, hopefully, the requisite root and branch nodes--and even leaf nodes are already there when needed, thus allaying the need for I/O's.  He can check it out here:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003139228.htm

Art, best of luck in all your SAS endeavors!

I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or methodologies specified in this posting are for illustrative purposes only and no warranty is stated or implied as to their accuracy or applicability. People deciding to use information in this posting do so at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance"
E-mail: MichaelRaithel@westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second Edition
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Only in America - do we buy hot dogs in packages of ten and buns in packages of eight. - Anonymous (but likely hungry)!
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++