Sorry, but this is a bit on the long side.....

At 02:48 PM 12/9/97 P, Steve Dirickson b894 WestWin wrote:
>Again I'd love to see someone who has actually used this capability
>provide verifiable hard numbers on the performance impact.

Unfortunately during the BETA testing we conducted on IMAGE b-trees
we did not focus on performance, but rather, functionality.  On the
small to medium (<1GB) masters we used from our test suites we did
not see any noticeable impact.  But I also must add, the relatively
small amounts of data used had a fairly good distribution and as
such, were not geared towards clustering which is where I believe
b-tree costs will start to become noticeable.

>Even more than the disk-space issue, the "performance problem" is,
>intuitively, a specious red-herring excuse.

I have to disagree.  There have been other postings to this list
whereby individuals utilizing KSAM have experienced a reduction
in performance when adding entries as the volume of data in the
file grows.  If memory serves me, in one instance this was in the
neighborhood of 100,000 entries or so.  This is the same KSAM
processing that is done when a DBPUT to a master (explicit or
implied via AUTO master) is performed. As such, applying these
same conditions to IMAGE B-trees implies that one can expect
similar problems to occur depending upon the specifics of the
conditions that exist when adding a new entry to the corresponding
master set.

If I recall, in some instances the user was able to improve
their performance by changing the KSAM file's blocking.  With
IMAGE B-trees, one does not have a means to specify any type
of blocking characteristic during creation via DBUTIL.  I don't
know if this would be useful or not, only that it seemed to make
a difference in one individual's case previously discussed here.

Also keep in mind that the implementation of b-trees relies on a
single KSAM file.  And NMKSAM places both the b-tree structures,
overhead and data in a single file.  This file is also currently
limited by MPE's 4GB file limit.  It is indeed possible to have
a non-jumbo master that is not 100% full and is < 4GB whose
key volume exceeds the capacity of the KSAM file.....  Conversely,
it is possible to have a jumbo master with > 4GB of data that can
easily be indexed with the single KSAM file.  So the clustering
potential of the KSAM index may occur before any noticeable issue
arises with the corresponding master set.

The KSAM file will now be a part of the same XM transaction that
covers the IMAGE transaction. I suspect that if a user's IMAGE
transaction is close to an XM-overflow and they now incorporate
b-trees on a master involved in that same transaction, it may
indeed have an XM-overflow. I'm also thinking that a large IMAGE
transaction with a large KSAM file that has to perform a
level-split (leaf add for re-balancing) may be pushed into an
XM-overflow.  These are things that are pretty much unknowns
because one cannot accurately predict all the transaction
conditions which may occur.  I only point this out because I
know there have been issues with the XM buffer size in the past
related to IMAGE transactions and that HP has investigated
enhancing its size.

Also, there are master sets with the types of data which do not
lend themselves to using b-tree indexes.  For example, consider a
master set keyed by social security number and used to verify
medical coverage, or a parts master keyed by part number. If
these sets were indexed by name or description it would make
more sense to use b-trees in order to perform partial retrievals.
However, those applications I've seen utilize a third-party product
or external KSAM for indexing the name field, returning the SS#
or part number to retrieve the actual master record.  In this
manner the entry can be retrieved on both items.  Adding IMAGE
b-trees and taking advantage of them would mean changing the
application and losing a retrieval key or adding another master
to support the other retrieval key. Clearly doing a DBFIND mode 1
using a SS# on a b-tree index to get the same key value and then
perform the calculated DBGET on a master to retrieve the entry
only adds overhead without adding benefit.

I've worked with customer bases which have master sets in excess
of 50,000,000 entries and are 24x7 shops on >4-way systems. They
simply cannot afford taking any risks until they've done extensive
testing/etc. They definitely would not want something like b-trees
automatically placed on every base/master.  And if they elect to
use this new feature, they will probably want to selectively place
it where they intend to use it to add benefit.

In my experience, I've seen much larger amounts of data maintained
in IMAGE sets that in KSAM files.  However, now that these have
been integrated we will now start to see larger KSAM files.  And
as such, we'll probably start to uncover more information in terms
of how KSAM responds under these conditions.

As such, I disagree that performance concerns are a 'red herring'
excuse for having b-trees on by default.  There is always a cost
for adding functionality, whether it is increased space needs or
the CPU cost of providing the functionality.  Many will not
experience any problems with IMAGE b-trees when they use them.
This is what HP and all the BETA testers hope is the case and why
so much time and effort was placed in testing and re-testing.

However, others will subject the new feature to scenarios that were
unanticipated or will push the functionality to new limits (heights!).

Don't get me wrong....I agree that IMAGE b-trees are a great
enhancement!  Only let's not gloss over the risks and costs
involved, especially as it may affect those large installations.
It has been these larger applications which have played an
important role in the continuation of the HP3K. And because
of the HP3K's world-renown stability and performance these
shops have been quite successful, thus expanding the visablity
of the HP3K.

Only I do disagree with Wirt and Steve's assertion to impose
b-trees be enabled by default for every situation without the
users/customer explicitly taking action. Just because ALLBASE
has SSI indexes by default doesn't mean IMAGE has to have the
same.  IMAGE is used in many circumstances over ALLBASE simply
because of OLTP performance. While imposing b-trees everywhere
may bring IMAGE 'upto' ALLBASE's functionality, it may also
'lower it' to ALLBASES's OLTP performance..... (OK Gary, I
have my armor on... :-)



/jf
                              _\\///_
                             (' o-o ')
___________________________ooOo_( )_OOoo____________________________________

                     Wednesday, December 10th

            Today, in 1817 - Mississippi became the 20th state.
                      1898 - Spain ceded the Phillippines to the U.S.

___________________________________Oooo_____________________________________
                            oooO  (    )
                           (    )  )  /
                            \  (   (_/
                             \_)