HP 3000 Manuals

HP ALLBASE/BRW Reference Manual

Data Access Methods 

This section describes the data access methods that you can use to
improve performance.  The emphasis here is on access methods for reports
using joined source tables.

Data Access Methods For Joins 

Joining datasets, files or HP ALLBASE/SQL tables means that their records
are combined such that records are joined where the common items in the
relation definition have identical values.  This is described in Chapter
6, Defining Data Access.

HP ALLBASE/BRW provides two different data access methods for joins,
which can be used singly or in combination.  These are discussed below.

Data Access Methods For Merges 

HP ALLBASE/BRW uses serial access to HP ALLBASE/SQL tables, IMAGE
datasets, KSAM and MPE files which are to be merged.  Datasets, files or
HP ALLBASE/SQL tables files are accessed in the access sequence specified
on the Tune Access screen.

Keyed Access For Joins 

Keyed access can be used for IMAGE datasets and KSAM files which are
joined.  In addition, IMAGE datasets, KSAM and MPE files can be accessed
via logical record number, which is also a type of key.  When a key is
specified, HP ALLBASE/BRW uses:

   *   chained access to IMAGE detail datasets

   *   calculated access to IMAGE master datasets

   *   keyed access to KSAM files

   *   direct access if the key is the item table-rec-number 


          Keyed Access for a Joined Table 

Since keyed access causes random access to datasets and files, it results
in good execution performance, if any of the following are true: 

   *   The report selects a small amount of data out of a large amount,
       for example, 5,000 out of 300,000 records.

   *   The accessed datasets or files are small enough to be buffered to
       a high degree by the disk caching.

Serial Access For Joins 

Serial access can be used for IMAGE datasets, HP ALLBASE/SQL tables, KSAM
or MPE files which are joined.  For best effects, HP ALLBASE/BRW uses the
following method to perform serial access with joined tables:

   *   All datasets, files or HP ALLBASE/SQL queries are copied into
       temporary workfiles.  During the copy, all possible selections and
       table calculated items are performed on single datasets, files or
       queries.  Also, only items actually used in the report are written
       to the temporary work files.

   *   All temporary work files are sorted by common items in the
       relation definitions.

   *   The temporary work files are joined by reading them in parallel in
       one pass.


          Serial Access 

Although serial access requires three passes over the data, it may be
necessary if common items do not have keys.

If the selections in the report can be performed when each dataset or
file is read, serial access causes a more effective access profile to the
disks (minimum head movements, fewer block transfers) compared to keyed
access, which causes more block transfers and head movements on the disks
(see Keyed Access, above).

For a small amount of data, serial access is slower than keyed access
because of the additional passes over the data.

Access Sequence For Joins 

The sequence in which the datasets, files, or HP ALLBASE/SQL tables are
accessed can also influence the report execution performance.  You should
choose an access sequence that can reduce the amount of data as early as
possible.  See the compile listing, discussed later in this chapter, for
when and which selections are performed.

For keyed access, the data selections should be performed as early as
possible; that is, datasets or files which contain items used for
selections should be accessed first.

For serial access, the access sequence does not have as much impact on
execution performance, but you can follow the same rules as for keyed
access.

Access Sequence For HP ALLBASE/SQL Tables 

If HP ALLBASE/SQL tables have different access sequence numbers, a query
is sent to HP ALLBASE/SQL for each table.  HP ALLBASE/SQL tables with the
same access sequence number are combined to one HP ALLBASE/SQL query.
[REV BEG]

In general, it is more efficient to combine all HP ALLBASE/SQL tables
from a single DBEnvironment into a single query by giving them all the
same access sequence number on the Tune Access screen.  This allows HP
ALLBASE/SQL to apply internal optimization and internal SQL indexes to
join the tables in the most efficient way.

If separate queries are given for each HP ALLBASE/SQL table, HP
ALLBASE/BRW will have to save the results of these queries into temporary
files and sort these temporary files on the common relation items before
combining the tables.


CAUTION  There are exceptions to the general rule that multiple HP
         ALLBASE/SQL tables should be combined into a single SQL query.

         If the common relation items joining all the tables in the HP
         ALLBASE/BRW report do not include items directly relating two
         SQL tables, HP ALLBASE/BRW will generate an SQL query such as
         the example shown on the following page.

              SELECT SQLA.item1,SQLB.item2 FROM SQLA,SQLB;

         Since there is no WHERE clause, HP ALLBASE/SQL is forced to
         combine every record in SQLA with every record in SQLB, a
         condition called a production.  Productions are very inefficient
         for tables with a large number of records.  For instance, if
         SQLA has 5000 records and SQLB has 10000 records, the SQL SELECT
         statement listed above would return 50,000,000 records (5000
         times 10000)!

         In this case, it is more efficient to use separate access
         sequence numbers to generate two SQL SELECT statements such as
         the following:

              SELECT SQLA.item1 FROM SQLA;

         and

              SELECT SQLB.item2 from SQLB;

         Let HP ALLBASE/BRW join this information to the non-SQL data in
         the report.


When combining HP ALLBASE/SQL data with HP IMAGE/3000 or KSAM data, it is
usually best to use the access sequence numbers in the Tune Access screen
to load the HP ALLBASE/SQL tables first.

The Tune Access screen does not accept key items for HP ALLBASE/SQL
tables, so putting them first allows keyed joins by giving key items for
the following HP IMAGE/3000 or KSAM tables.[REV END]

MPE/iX 5.0 Documentation