HP 3000 Manuals

HP ALLBASE/BRW Tutorial

Exercise 3:Understanding Keyed Access 

One of your tasks for this lesson requires that your reports use keyed
access and that you demonstrate the performance improvements of keyed as
against serial access.  The performance improvements will only be
apparent once the report is run.  Therefore, this comparison, and a
detailed examination of access tuning and methods, are discussed at the
end of Lesson 4, when you run the report.  This exercise takes about 15 
minutes. 

The first part of this exercise is a reading exercise.  Read the
following paragraphs for an understanding of keyed access versus default
access.

Default Access 

By default, BRW reads each source table serially, in the order specified
on the Define Table screen.  BRW reads each table serially into a
workfile.  It then sorts the workfile, using the common item defined in
the relation as the sort key.  For example, the Define Table screen for
ORDER-PRODUCTS lists the tables in the order PRODUCTS, ORDER-DETAILS, and
ORDERS. BRW reads the PRODUCTS data set into a workfile.  This file is
sorted on PRODUCT-NO. It then reads the data set ORDER-DETAILS into a
second workfile, and sorts it on PRODUCT-NO. Then it joins the two
workfiles, on PRODUCT-NO, and writes the resulting records into a third
workfile.

Tuned Access 

You can, however, specify both the order in which the data sets are read,
and the method of access.  For example, you know that you want to read
all the records in ORDERS, so that you can determine which orders were
for June.  It is better to read this data set serially, because you want
to read all the records in it.

But note data set ORDER-DETAILS. You only want to access the items in
this data set that match the ORDER-NO in ORDERS. So you can use keyed
access to ORDER-DETAILS, on the item ORDER-NO. That is, when you have
found the values of ORDER-NO in ORDERS that apply to June, you can use
those values as keys into the data set ORDER-DETAILS. In this way, you do
not have to read ORDER-DETAILS into a workfile and sort it.  Using keyed
access, you know that you will directly retrieve the records that you
want.

The same is true of data set PRODUCTS. You only want information on those
products that have been ordered in June, that is, that are present in the
table ORDER-PRODUCTS. So, if you use keyed access on PRODUCT-NO, using
the values of PRODUCT-NO found in ORDER-DETAILS, you will automatically
have the records that you want without any sorting.

So, by altering the sequence in which the tables are accessed, and by
using keyed access on PRODUCTS and ORDER-DETAILS, you can improve report
performance.  You tune the report's performance on the Tune Access
screen.

Use Tuned Access 

   1.  Choose  Tune Access to display the Tune Access screen.


            

       The three source tables are listed in the order in which they
       appear on the Define Table screen.  The default access sequence
       (the order in which they will be read) is this order.  But you
       want to read ORDERS first, followed by ORDER-DETAILS, and finally
       PRODUCTS.

   2.  Type 3 in the Access Sequence field beside PRODUCTS.

   3.  Leave the 2 in the Access Sequence field beside ORDER-DETAILS.

   4.  Type 1 in the Access Sequence field beside ORDERS.

   5.  Press Enter.

BRW tells you that the access sequence has been modified.

Specify Keyed Access 

Now you want to specify keyed access on the source tables PRODUCTS and
ORDER-DETAILS.

   1.  Type PRODUCT-NO in the Key field beside PRODUCTS,

   2.  Type ORDER-NO in the Key field beside ORDER-DETAILS.

   3.  Press Enter.

       BRW will use keyed access on the tables.  If the source table is a
       master data set, a calculated read is used, if the source is a
       detail data set, a chained read is used, and if the source table
       is a KSAM file, keyed access to the KSAM file is used.  BRW tells
       you immediately if you try to use keyed access on an item that is
       not a key.


            

       BRW tells you that the access sequence has been modified.

   4.  Choose  Exit twice to return to the Define Report screen.


            

Note that the new final access table is CUSTREP3-DATA.

MPE/iX 5.0 Documentation