HP 3000 Manuals HP 3000 Manuals
Report Performance
This exercise explains how to read the compile listing and execution
statistics, and how BRW accesses tables. This exercise also explains
access blocks, the use of keyed access, and how to improve report
performance. This exercise takes 30 minutes.
You have written and run the report, now you need to show the differences
in performance when using keyed access. For this comparison, you need
the compilation listing and execution statistics from lesson 3, because
the report in lesson 3 does not use keyed access. If you do not have a
printout of them, run the report again and obtain them. Or use the
example of the compilation listing and execution statistics from Lesson 3
shown below.
Lesson 3 Compilation Listing
______________________________________________________________________________________
| |
| HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992|
| |
| |
| Configuration File opened is BRWCONF.PUB.SYS |
| |
| Specification File: CUSTREP2/.BRWSPEC.ITF3000 |
| Selection Set : |
| Execution File : CUSTREP2/.BRWEXEC.ITF3000 |
| |
| |
| D A T A A C C E S S S T R U C T U R E |
| ========================================= |
| CUSTREP2-DATA (WORK007) join of: |
| -------------------------------- |
| WORK005 |
| CUSTOMERS,TOYDB.PUB serial |
| |
| ORDER-PRODUCTS (WORK006) join of: |
| --------------------------------- |
| WORK003 |
| PRODUCTS,TOYDB.PUB serial |
| |
| ORDER-TABLE (WORK004) join of: |
| ------------------------------ |
| WORK001 |
| ORDERS,TOYDB.PUB serial |
| |
| WORK002 |
| ORDER-DETAILS,TOYDB.PUB serial |
| |
______________________________________________________________________________________
________________________________________________________________________
| |
| T A B L E C A L C U L A T I O N S A N D S E L E C T I O N S|
| =================================================================|
| CUSTREP2-DATA (WORK007) |
| WORK005 |
| |
| ORDER-PRODUCTS (WORK006) |
| |
| WORK005 |
| CUSTOMERS,TOYDB.PUB |
| |
| ORDER-PRODUCTS (WORK006) |
| WORK003 |
| |
| ORDER-TABLE (WORK004) |
| |
| WORK003 |
| PRODUCTS,TOYDB.PUB |
| |
| ORDER-TABLE (WORK004) |
| WORK001 |
| |
| WORK002 |
| |
| WORK001 |
| ORDERS,TOYDB.PUB |
| |
| Selection: month_of (ORDERS.ORDER-DATE) = 6 |
| |
| WORK002 |
| ORDER-DETAILS,TOYDB.PUB |
| |
________________________________________________________________________
Compilation Listing (Continued)
Lesson 3 Execution Statistics
______________________________________________________________________________________
| |
| HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992|
| |
| KSAM (A.07.03) |
| |
| Configuration File opened is BRWCONF.PUB.SYS |
| Execution File: CUSTREP2.BRWEXEC.ITF3000 |
| Parameters: |
| &daklt; |
| |
| |
| Access Block 1: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-dset serial ORDERS,TOYDB 15 0.026 0.028|
| write WORK001 14 0.067 0.213|
| sort WORK001 14 0.019 0.019|
| process 0.344 0.807|
| =========== ===========|
| time totals for block 0.456 1.067|
| (0 segment loads for block ) |
| |
| |
| Access Block 2: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-dset serial ORDER-DETAILS,TOYDB 15 0.028 0.026|
| write WORK002 15 0.076 0.244|
| sort WORK002 15 0.016 0.015|
| process 0.034 0.036|
| =========== ===========|
| time totals for block 0.154 0.321|
| (0 segment loads for block ) |
| |
| |
| Access Block 3: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-dset serial PRODUCTS,TOYDB 10 0.027 0.024|
| write WORK003 10 0.074 0.237|
| sort WORK003 10 0.014 0.015|
| process 0.035 0.034|
| =========== ===========|
| time totals for block 0.150 0.310|
| (0 segment loads for block ) |
| |
| |
| Access Block 4: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-mpe serial WORK001 14 0.032 0.093|
| read-mpe search WORK002 14 0.034 0.097|
| write WORK004 14 0.073 0.269|
| sort WORK004 14 0.015 0.015|
| process 0.047 0.044|
| =========== ===========|
| time totals for block 0.201 0.518|
| (0 segment loads for block ) |
| |
______________________________________________________________________________________
______________________________________________________________________________________
| |
| Access Block 5: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-dset serial CUSTOMERS,TOYDB 8 0.031 0.029|
| write WORK005 8 0.065 0.367|
| sort WORK005 8 0.014 0.014|
| process 0.092 0.184|
| =========== ===========|
| time totals for block 0.202 0.594|
| (0 segment loads for block ) |
| |
| |
| Access Block 6: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-mpe serial WORK003 10 0.031 0.093|
| read-mpe search WORK004 14 0.031 0.093|
| write WORK006 14 0.066 0.260|
| sort WORK006 14 0.015 0.015|
| process 0.047 0.049|
| =========== ===========|
| time totals for block 0.190 0.510|
| (0 segment loads for block ) |
| |
| |
| Access Block 7: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-mpe serial WORK005 8 0.030 0.085|
| read-mpe search WORK006 14 0.032 0.075|
| write WORK007 16 0.074 0.453|
| sort WORK007 16 0.021 0.020|
| process 0.047 0.052|
| =========== ===========|
| time totals for block 0.204 0.685|
| (0 segment loads for block ) |
| |
| |
| Access Block 8: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-mpe serial WORK007 16 0.036 0.110|
| print CUSTEXE2 86 0.325 0.744|
| process 0.087 0.091|
| =========== ===========|
| time totals for block 0.448 0.945|
| (1 segment loads for block ) |
| |
| |
| Report Info : cpu-sec elapsed-sec|
| =========== ===========|
| time totals for report 2.005 4.950|
| |
| (2 segment loads for report) |
| (2 segments in report ) |
| (2 segments in memory ) |
| |
______________________________________________________________________________________
Execution Statistics (Continued)
Access Blocks and Workfiles
The BRW reporting process is split into steps called access blocks. Each
block has one or more input files and an output file (workfile). A
workfile is sorted if it is to be used later in a join. The workfiles
are temporary files, called WORKnnn. That is, the first workfile used is
called WORK001, the second WORK002, and so on. The final workfile is the
final access table for the report.
Access Structure
You can see the structure of the report access using the compilation
listing. This shows the order in which the data is accessed, and how it
is accessed.
Performance Information
Performance information is also found in the execution statistics.
Whenever you run a report, execution statistics are printed to the
standard list device. This listing shows the following information for
each access block:
* Which files are input.
* How each input file is read.
* The number of records read.
* To which workfile the block's output is written.
* The number of records written.
* Whether or not sorting is done.
* The CPU time and elapsed time that was required to accomplish each
of these tasks.
This listing can be used to help find the most efficient way to produce a
particular report. You can reduce the time taken to perform a report in
the following ways:
* By reducing the number of access blocks.
* By reducing the time taken to sort, read, and write files.
* By reducing the number of records used in the report as early as
possible.
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. If the file is to be joined, BRW sorts the workfile, using the
common item defined in the relation as the sort key.
Lesson 3 Execution Statistics Explanation
Look at the execution statistics for lesson 3.
* In the first Access Block, BRW reads the ORDERS data set serially
and writes it into a work file, WORK001. This workfile is sorted
on ORDER-NO.
* In the second access block BRW reads the data set ORDER-DETAILS
into a second workfile, WORK002, and also sorts it on ORDER-NO.
* In the third access block, BRW reads the data set PRODUCTS
serially, writes it to a workfile WORK003 and sorts the workfile
on PRODUCT-NO.
* In the fourth access block, BRW joins the two workfiles WORK001
and WORK002 on ORDER-NO, and writes the resulting records into a
third workfile, WORK004. This workfile is then sorted on
PRODUCT-NO.
* In the fifth access block, BRW reads the data set CUSTOMERS
serially, writes it to workfile WORK005, and sorts it on
CUSTOMER-NO.
* In the sixth access block, BRW joins workfiles WORK003 and
WORK004, and writes the result to WORK006.
* In the seventh access block, BRW joins WORK006 with WORK005, to
produce the final access table, WORK007. This workfile is sorted
on the sort items that you defined on the Define Breaks screen,
that is on SALES-AREA, CUSTOMER-NO, and ORDER-NO.
* In the eighth access block, BRW reads the final access table
(WORK007), and processes and prints the report.
Note that the time taken and records selected are all clearly displayed
on the execution statistics.
Specifying The Access Sequence and Method
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.
Notice data set ORDER-DETAILS. You only want to access the records in
this data set that have an ORDER-NO that matches the ORDER-NO in ORDERS.
You can, therefore, 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. Using this method, you do not have to read ORDER-DETAILS
into a workfile, sort the workfile, and then join the workfiles. By
using keyed access, you know that you will retrieve exactly those record
values that you want, that is, those records in ORDER-DETAILS with
ORDER-NOs that match the values of ORDER-NO in ORDERS.
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
join of tables ORDERS and ORDER-DETAILS. 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.
(If you read PRODUCTS serially, you would have to read it into a
workfile, sort it on PRODUCT-NO, and then compare it, record-by-record,
with the records in ORDER-DETAILS. This could be very inefficient if
there were many products that had not been ordered in June.)
Keyed Access
So, by specifying keyed access on PRODUCTS and ORDER-DETAILS, you can
improve report performance.
You specified the access sequence and keyed access on the Define Tables
screen when defining the data access. The screen below shows the Tune
Access screen, after you completed defining the data access.
![[]](../../pic3k/M012045/FFN1783c50.gif)
To see how much more efficient this method is, compare the execution
statistics of lesson 3 (execution file SOLUTN2.BRWEXEC.ITF3000) with the
execution statistics of execution file SOL3DIR. The compilation and
execution statistics are shown below.
Lesson 4 Compilation Listing
______________________________________________________________________________________
| |
| HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992|
| |
| |
| Configuration File opened is BRWCONF.PUB.SYS |
| |
| Specification File: CUSTREP3/.BRWSPEC.ITF3000 |
| Selection Set : DIRECTORS |
| Execution File : DIRECTRS/.BRWEXEC.ITF3000 |
| *** WARNING: Parameter AREA not defined in selection set, |
| NO_VALUE used instead |
| |
| |
| D A T A A C C E S S S T R U C T U R E |
| ========================================= |
| CUSTREP3-DATA (WORK003) join of: |
| -------------------------------- |
| WORK001 |
| CUSTOMERS,TOYDB.PUB serial |
| |
| ORDER-PRODUCTS (WORK002) join of: |
| --------------------------------- |
| ORDERS,TOYDB.PUB serial |
| ORDER-DETAILS,TOYDB.PUB key: ORDER-No |
| PRODUCTS,TOYDB.PUB key: PRODUCT-No |
| |
| |
| |
| T A B L E C A L C U L A T I O N S A N D S E L E C T I O N S |
| ================================================================= |
| CUSTREP3-DATA (WORK003) |
| WORK001 |
| |
| ORDER-PRODUCTS (WORK002) |
| |
| |
| WORK001 |
| CUSTOMERS,TOYDB.PUB |
| |
| Selection: CUSTOMERS.CUSTOMER-NO satisfies ?CUSTOMER-NO |
| |
| ORDER-PRODUCTS (WORK002) |
| ORDERS,TOYDB.PUB |
| |
| Selection: month_of (ORDERS.ORDER-DATE) = 6 |
| and |
| ORDERS.CUSTOMER-NO satisfies ?CUSTOMER-NO |
| |
| ORDER-DETAILS,TOYDB.PUB |
| |
| PRODUCTS,TOYDB.PUB |
| |
______________________________________________________________________________________
Lesson 4 Execution Statistics
______________________________________________________________________________________
| |
| HP ALLBASE/BRW (A.01.31) HP35360 (c) Copyright Hewlett-Packard GmbH 1986-1992|
| |
| KSAM (A.07.03) |
| |
| Configuration File opened is BRWCONF.PUB.SYS |
| Execution File: DIRECTRS.BRWEXEC,ITF3000 |
| Parameters: |
| &daklt; |
| CUSTOMER-NO |
| &daklt; |
| |
| |
| Access Block 1: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-dset serial CUSTOMERS,TOYDB 8 0.033 0.031|
| write WORK001 8 0.081 0.397|
| sort WORK001 8 0.019 0.019|
| process 0.346 0.713|
| =========== ===========|
| time totals for block 0.479 1.160|
| (0 segment loads for block ) |
| |
| |
| Access Block 2: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-dset serial ORDERS,TOYDB 15 0.030 0.027|
| read-dset chain ORDER-DETAILS,TOYDB 14 0.070 0.291|
| read-dset calc PRODUCTS,TOYDB 14 0.025 0.024|
| write WORK002 14 0.067 0.247|
| sort WORK002 14 0.015 0.015|
| process 0.129 0.292|
| =========== ===========|
| time totals for block 0.336 0.896|
| (0 segment loads for block ) |
| |
| |
| Access Block 3: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-mpe serial WORK001 8 0.034 0.115|
| read-mpe search WORK002 14 0.031 0.079|
| write WORK003 16 0.072 0.482|
| sort WORK003 16 0.021 0.021|
| process 0.049 0.055|
| =========== ===========|
| time totals for block 0.207 0.752|
| (0 segment loads for block ) |
______________________________________________________________________________________
______________________________________________________________________________________
| |
| Access Block 4: #records cpu-sec elapsed-sec|
| ---------- ----------- -----------|
| read-mpe serial WORK003 16 0.036 0.099|
| print DIRECTRS 99 0.340 0.640|
| process 0.082 0.083|
| =========== ===========|
| time totals for block 0.458 0.822|
| (2 segment loads for block ) |
| |
| |
| Report Info : cpu-sec elapsed-sec|
| =========== ===========|
| time totals for report 1.480 3.630|
| |
| (3 segment loads for report) |
| (3 segments in report ) |
| (3 segments in memory ) |
| |
______________________________________________________________________________________
Execution Statistics (Continued)
Lesson 4 Execution Statistics Explanation
Look at the execution statistics for Lesson 4.
* In the first access block, BRW reads the CUSTOMERS data set
serially into WORK001, and sorts it on CUSTOMER-NO.
* In the second access block, BRW reads the ORDERS data set
serially. But, instead of writing the contents of ORDERS directly
into a workfile, BRW performs a chained read into the detail data
set ORDER-DETAILS, using as a key, the value of ORDER-NO obtained
from the serial read of ORDERS. Then, BRW performs a calculated
read on the master data set PRODUCTS, using as a key, the value of
PRODUCT-NO retrieved from the data set ORDER-DETAILS. Only then is
a record written to WORK002. Note that no separate workfiles or
sorts are needed for PRODUCTS or ORDER-DETAILS. The keyed access
retrieves exactly those records that would have been obtained by
the sort and join.
When each record of ORDERS has been read, and all the
corresponding entries in ORDER-DETAILS and PRODUCTS retrieved,
workfile WORK002 is sorted on CUSTOMER-NO.
* In the third access block, BRW joins WORK001 and WORK002 on
CUSTOMER-NO, and writes the resulting records to WORK003. This is
the final access table, and is sorted on the sort items you
defined on the Define Breaks screen, that is on SALES-AREA,
CUSTOMER-NO, and ORDER-NO.
* In the fourth access block, BRW reads the final access table, and
processes and prints the report.
Why Does the Third Report Execute Faster?
You can see that the number of access blocks and workfiles has been
reduced, and the report executes significantly faster, even with a small
example database like TOYDB. This is because, using keyed access:
* You avoided reading the entire PRODUCTS and ORDER-DETAILS data
sets; you just extracted the records you wanted.
* You avoided writing two workfiles for the data sets PRODUCTS and
ORDER-DETAILS.
* You avoided sorting the two workfiles for the data sets PRODUCTS
and ORDER-DETAILS.
* You avoided the join operations joining ORDERS with ORDER-DETAILS,
and ORDERS/ORDER-DETAILS with PRODUCTS.
You avoided four entire access blocks and four workfiles. Note how easy
it is to see where the time is taken, and to demonstrate the performance
improvements. Because the execution statistics are so detailed, it is
very easy to experiment with access methods, and very easy to use
different access methods using the Tune Access screen.
Performance Summary
Performance tips are listed in the Performance Considerations chapter of
the HP ALLBASE/BRW Reference Manual. Many other useful examples of keyed
access are also shown in the Defining Data Access chapter of the HP
ALLBASE/BRW Reference Manual.
There are too many variables involved in report production to provide a
hard and fast rule for the most efficient specification of a report.
However, you can use the following general guidelines:
* Keyed access is generally more efficient than serial if a subset
(< 30%) of data is selected.
* Reduce data as early as possible in the process.
* Reduce the number of physical block transfers to and from discs.
* Reduce the number of head movements on discs through efficient
access.