Improving the Performance of Views (SAP Library - Data Retention Tool (DART) (CA-GTF-DRT))

Improving the Performance of Views

Use

When retrieving the data for a view's report the system accesses, by default, the segments included in the view in the order that they are listed in the view definition.

You may choose to let the system optimize access to the segments in the view when running the view report. You do so by flagging the Optimize access sequence option on the Data File View screen.

If the performance of a certain view is slow, even when the system optimizes access to its segments, then you can try to improve its performance by specifying the access sequence to the segments yourself as described below.

You can also improve the performance of a view by defining appropriate indexes on the segments that are included in the view as described below.

Prerequisites

You must be familiar with the data and the segments used in the view.

Procedure

Determine the access sequence to the segments in the view using the guidelines listed below (see the section on Guidelines).

Define the access sequence by using one of the following methods:

When defining the view, by listing the segments in the order they should be accessed, or by assigning a number next to the segments in the Nr. Field
When executing the view, by choosing the Use specified access sequence option on the Extract view screen, and entering a number, if necessary

Create indexes on the fields that are used to join the segments in the view. Use the access sequence and the rules below to determine which fields to index. If no indexes are available (based on default segment order), the system will send a message when checking and saving the view definition.

Define the indexes in the definition of the appropriate segment.

Guidelines

Follow these guidelines to achieve the best performance for a given view. See the Example section following the guidelines.

Access Sequence

When ordering the segments in a view, your goal is to narrow down the number of records accessed as soon as it is possible.

If the view selects all the records of all of its segments, then access should be from the more general segment to the more specific segment.
If the view filters some of the records, then place the segment that results in the least amount of records first in the access sequence.
Place a segment after another only if the second segment has a direct join to the first segment. In other words, do not place two segments together in the sequence if the system has to use another segment to find the connection between these two segments.

Indexes

You define indexes to speed the process of finding a related record from the next segment in the access sequence.

Using the access sequence you have defined for the segments in the view, define an index on a field that joins each segment with its previous one. Starting with the second segment in the view, define an index on the field that joins the second segment with the first one. Create an index on the field in the third segment that joins the third segment with the second, and so on.
You do not need to define an index for company code and posting period fields in any segment. The system indexes these fields automatically.
If possible, define only one index for every pair of segments in the view. If there are several join conditions for a pair of segments, then define an index for the field that has the highest selectivity, that is, the field that contains a wider variety of values. Therefore, selecting a value from this field results in a smaller amount of records.

Example

Consider a view containing the following segments:

Segment Name

Segment Description

TXW_FI_HD

FI document header

TXW_FI_POS

FI document item

TXW_GLACC

G/L account master

The following table lists the join conditions between these three segments and the fields used for these join conditions:

Segment Name	Segment Name	Join Field
TXW_FI_HD	TXW_FI_POS	Document number (BELNR in both segments)
TXW_FI_HD	TXW_FI_POS	Company code (BUKRS in both segments)
TXW_FI_HD	TXW_FI_POS	Posting period (MONAT in both segments)
TXW_FI_POS	TXW_GLACC	G/L account (HKONT in TXW_FI_POS, SAKNR in TXW_GLACC)

Access Sequence

Using the example segments above, you do the following to improve access to the view:

If the view selects all the records of all of its segments, then access should be from the more general segment to the more specific segment.

If you define a view to display all of the FI document records, it is best to place the FI document header first, then the document item and then the G/L account. The system accesses every document header, then searches for all of its items, and then for the relevant G/L account master records.

If the view filters some of the records, then place the segment that results in the least amount of records first in the access sequence.

If the view looks at only a few G/L accounts in the above example, then the following access sequence is the best:

1. G/L account master

2. FI document item

3. FI document header

When a view defines a small set of G/L accounts to look at, then accessing the G/L account master first immediately eliminates all the irrelevant G/L account master records. From this smaller set of G/L account master records the system finds the related FI document item records, and then the related FI document header records. The system does not look at irrelevant document header or line item records. Note that only FI document items include the account number, and not the document header.

Place a segment after another only if the second segment has a direct join to the first segment. In other words, do not place two segments together in the sequence if the system has to use another segment to find the connection between these two segments.

Do not use the following sequence:

1. G/L account master

2. FI document header

3. FI document item

An FI document header record does not have a direct join with the G/L account master record. For the system to find the relevant FI document header record for any given G/L account master record, the system must first find the relevant FI document item record. The system has to access all of the FI document header records, and within each of them it has to scan all of their items for the relevant record. The system has to repeat this process for every G/L account master record. This could result in the worst performance possible for the view.

Indexes

Using the access sequence you have defined for the segments in the view, define an index on a field that joins each segment with its previous one. Starting with the second segment in the view, define an index on the field that joins the second segment with the first one. Create an index on the field in the third segment that joins the third segment with the second, and so on.

Take the following sequence as an example:

1. G/L account master

2. FI document item

3. FI document header

Defining an index on the G/L account field (HKONT) of the FI document item segment speeds access to the relevant FI document item from a given G/L account master record.

Defining an index on the Document number field of the FI document header segment speeds access to the relevant FI document header from a given FI document item record.

Note that determining which fields to index depends on the access sequence. You need to index different fields if you define the access sequence as follows:

1. FI document header

2. FI document item

3. G/L account master

In this second sequence example you should place an index on the Document number field of the FI document item segment, and not on the parallel field in the FI document header segment as in the previous sequence. The second index should be on the G/L account field (SAKNR) of the G/L account master segment, and not on the parallel field in the FI document item segment as in the previous sequence.

You do not need to define an index for company code and posting period fields in any segment. The system indexes these fields automatically.

In the FI document example, the system indexes the company code and posting period automatically. You should not specify an index for them in the segment definition.

If possible, define only one index for every pair of segments in the view. If there are several join conditions for a pair of segments, then define an index for the field that has the highest selectivity, that is, the field that contains a wider variety of values. Therefore, selecting a value from this field results in a smaller amount of records.

For example, if you combine the AM Asset document header and the AM Asset document item segments in a view, the following are the default join conditions:

Asset
Sub-number
Sequential number
Company code
Posting period

Since you do not create an index for company code and posting period, you have a choice between the Asset, Sub-number, and Sequential number fields for placing an index that will improve the performance of that view.

The Asset field contains unique asset numbers. It therefore has a higher selectivity than the two other fields, which use a smaller set of values. Indexing the Asset field will speed access to the relevant Asset document item record given a particular Asset document header record, for example.

Segment Name	Segment Description
TXW_FI_HD	FI document header
TXW_FI_POS	FI document item
TXW_GLACC	G/L account master