Code Inspectors Performance Checks (IIII)
In the previous blogs of this series (see below) the objects that were investigated with the Code Inspector were ‘program-like’, that is reports, function groups, or ABAP OO classes.
Code Inspector performance checks series:
Table attributes check (discussed in this blog)
But the tool can do a lot more to improve the software quality than just analyzing ABAP source code. In principle, all types of repository objects can be checked with respect to consistency of their technical properties.
In this blog I want to present the ‘Table attributes check’ of the Code Inspector, which analyzes the definition of ABAP data dictionary tables and views.
The properties of a database table or a database view are maintained within the ABAP dictionary (transaction SE11), for example on the tab strip ‘Delivery and Maintenance’ or in the ‘Technical Settings’. Unfortunately, the ABAP dictionary does not assist you to choose table properties that are consistent and reasonable from the performance point of view.
The Code Inspector ‘Table attributes check’ provides messages if technical properties, buffer settings, or index settings appear to be dissonant. Please note that the Code Inspector has a simple rule set to identify such real or just would-be inconsistencies. Some of the simple rules of the ‘Table attributes check’ might need to be overruled by the wise developer in special cases.
Here is a short introduction into the most important technical parameters of a table or view in the data dictionary:
This parameter determines, how the data in the table will behave during system installation, upgrade, client copy, and in a transport between systems. The most important delivery classes are A for application data and C for configuration data. Further delivery classes are L, G, E, S , and W. You can get more information on the delivery class within the fields’ F1-help on tab strip ‘Delivery and Maintenance’ in transaction SE11.
For some database platforms, this parameter determines in which physical area of the database the table will be created. The Code Inspector tool uses the data class as a categorization of the table with respect to it’s data content. Since this influences some of the tools’ check results, you should correctly maintain whether a table contains master data (data class APPL0), transactional data (APPL1), or configuration data (APPL2).
The size category is also maintained in the technical settings and is used to determine and reserve the initial size of a table in the database. The Code Inspector uses this parameter as an indicator for the real size of the table in production use.
This parameter determines the type of a table; the relevant ones for this check are TRANSP (transparent table), CLUSTER (cluster table), POOL (pooled table), and VIEW (general view structure).
Buffering and Buffering types
Tables that are frequently read during production use, but only rarely modified, such as those containing configuration data, should be buffered on the application server. The access to data in a buffered table is 10-50 times faster than accessing data in a non-buffered table, being read from the database cache. In the technical settings of a table in transaction SE11, you are able to turn on the table buffering and select the buffer type (fully buffered, generically buffered, or single record buffering).
In the following diagram I depicted, how delivery class, data class, and buffering should be combined for (most of) the tables in your system. This diagram also reflects some of the rules according to which the Code Inspector ‘Table attributes check’ evaluates dictionary tables. As mentioned, there might be a few tables that do not fit into this scheme.
- delivery class C (customizing table) should not be combined with data class APPL1 (transactional data)
- a table that contains configuration data (APPL2), and which is assigned to delivery class S (system table), should be buffered
- a master data table (APPL0) with delivery class A (application table) should not be buffered, except if it has only a few entries (size category 0, or single record buffered with size category 0, 1, or 2)
Frequently executed database statements must be supported by a database index to allow a fast search of the required entries. There are some rough rules for database indexes: for example, there should not be too many indexes defined for one table. Also, two different indexes of one table should not have too many common fields.
Messages of the check
In the following I want to present the messages of the ‘Table attributes check’ and give you some hints what can be done to improve.
Messages 0001, 0002: No Table Class or Delivery Class Chosen; No Data Class or Size Category Chosen
Maintain these table categorizations. They are used by the Code Inspector to evaluate database accesses.
Messages 0003, 0004: “Buffering Permitted, but switched Off” Selected
From a performance point of view, this buffer setting should be omitted. You as developer should know whether some table is suited for buffering or not. Only if a table might be small at customer A and very large at customer B, this setting makes sense and should be selected.
Message 0010: Buffering Type is Initial but Delivery Class Is “C”, “G”, “E” or “S”
According to it’s delivery class, the reported table is a configuration table (C or G), or a system table (delivery class E or S). Such tables are normally small and get rarely changed. Therefore they should be buffered. If the reported table does not contain configuration data or system data, you may have assigned the wrong delivery class to the table.
Message 0011: Buffering is Activated but Delivery Class Is “A” or “L”
According to it’s delivery class, the reported table contains application data (A), that is master or transactional data, or it is a table with temporary data (delivery class L). In both cases it is expected that the table has many entries and is changed frequently. Normally, such a table should not be buffered. Eceptions are small master data tables, which might also be buffered. If the reported table does not contain the described type of data, the delivery class might be wrongly assigned.
Message 0012: Buffering is Activated but Size Category Is > 2
Very large tables should not be buffered, even if the other preconditions for buffering are fulfilled. One effect of buffering large tables is that numerous small buffered tables might be displaced from the SAP table buffer. If only a few entries of a large table are needed during program execution, it can be reasonable to choose single record buffering or a fine granular generic buffering for this table. If the reported table has only a few entries in production use, and buffering would be appropriate, you might have assigned a wrong size category to the table.
Messages 0013, 0014: Buffering is Initial but Data Class Is “APPL2”; Buffering is Activated but Data Class Is “APPL0″/”APPL1”
Data class and buffering must be consistent. Normally, tables with data class APPL2, such as configuration tables, should be buffered. Tables of data class APPL0 or APPL1, such as master data tables or transactional data tables, should not be buffered. As an exception from this rule, small master data tables may also be buffered.
Message 0015: Buffering is Activated but No Buffering Type Is Chosen
Choose an appropriate buffering type for the reported table, based on the planned accesses. Possible buffering types are fully buffered, generically buffered, and single record buffered.
Messages 0016, 0017: Buffering is permitted but table is contained in DB view db_view; Buffering is permitted but table can be changed using database view db_view
Until NetWeaver release 6.40, it was very critical if modification of a buffered table was allowed via a database view. The reason for this was, that data changes for the view did not lead to an invalidation of the table buffer – which could lead to data inconsistencies when reading data from the buffered table. Since release 7.0 the buffer is also invalidated, if there are changes for the database view.
But what about the first message – why is it bad, if there is a database view defined for a buffered table? The rational is that if some developer decided for the additional load that is implicated by activating the buffer option, most accesses to the table really should make use of the buffering. But the existence of a database view for the table indicates that also other accesses, which are not using the buffer, are planned.
Last two comments on this check: the messages are always reported for the buffered table, not for the database view. One reason is that the ‘owner’ of the table should have the control on how the data is accessed. She or he should be aware of alternative access strategies that might be implemented by other developers.
If the database view is also buffered, there is no message reported. But note that any invalidation of the underlying table will also invalidate the buffered view in the table buffer.
Messages 0020, 0021: Table has more than 100 (700) fields
For technical and design reasons, a database table should not have too many fields. For Business ByDesign the more strict value of 100 fields is applied, otherwise up to 700 fields are allowed.
Messages 0022, 0023: Change Log Active Despite Data Class “APPL0” or “APPL1”;
Change Log Active for Large Table
The change log for a table is activated in the technical settings by selecting the flag ‘Log data changes’. If the system parameter rec/client is also set for the client, a log entry is created for every change of the table. It’s clear that for master data tables and for tables with transactional data (data classes APPL0 and APPL1) no change log should be created. The same is true for large tables in general (size category > 2). Writing change logs for such tables would slow down the production system.
Message 0030: Table Has Unique Secondary Index
That’s just an information message with no performance relevance.
Every developer who inserts data into such a table should be aware, that the insertion of entries which are identical with respect to the secondary index will lead to a run time error ‘Insert Duplicate Keys’.
Messages 0031, 0034, 0131, 0134: Table Has More Than 4 (7) Secondary Indexes Though Data Class Is “APPL0”; Table Has More Than 2 (5) Secondary Indexes Though Data Class Is “APPL1”
A database table should no have too many secondary indexes. On the one hand, every additional index increases the cost for data insertion and modification (the latter only, if fields of the secondary index are affected). On the other hand, the cost based optimizer of the database, which calculates the data access strategies, might get puzzled by too many indexes.
Message 0032: Secondary Index sec_index: More Than 4 Fields
More than four fields can be okay for an index, for example if you are planning an index-only access. But as a rule of thumb, four or less fields should be sufficient. The client field is not counted by the check.
Message 0033: Table Has Secondary Index But Is Buffered
The existence of a secondary index for a table signals, that beside buffered accesses also secondary index accesses are planned. It must be ensured that the additional costs of both buffering and the secondary index are justified by corresponding table accesses in production use. The check only reports non-unique secondary indexes, since sometimes unique secondary indexes are defined to preclude duplicates in a table with respect to certain key combinations.
Message 0035: At Least 2 Fields (“dbfld_1” and “dbfld_2“) Are Included in 2 Indexes
Indexes should not have too many fields in common with other indexes. For example, it does not make sense to have a table with a (primary or secondary) index A for the fields dbfld_1 dbfld_2 and another index B for the combination dbfld_2 dbfld_1. A different story is an index on fields dbfld_1 dbfld_2 dbfld_3 – here it could be justified to have another index on fields dbfld_3 dbfld_2. This check demands the wise developer to find out what is the appropriate solution!
Message 0036: Index idx_1 contains index idx_2 (left-aligned)
If there is an index dbfld_1 dbfld_2 dbfld_3 it does not make sense to define another index with fields dbfld_1 dbfld_2.
Messages 0037, 0137: Table dbtab: Field fld_name in primary / secondary index idx_name is of the type FLOAT
When a field of type FLOAT is read from the database, rounding differences might occur during the value assignment to an ABAP data object. If such a field is part of the table’s primary key, this means that a certain table entry may not be addressed from within ABAP. If a field of type FLOAT is part of a secondary index, reasonable accesses are only possible with range conditions in the WHERE-clause of the database statement for this field.
Messages 0038, 0039: Client-specific table dbtab: Secondary index idx without client field; Client-specific table dbtab: Secondary index idx does not have client as first field
Though the client field is not very selective (and indexes should contain highly selective fields), we vote for putting it at the first position of any secondary table index, since normally the client is known for all table accesses. Secondary indexes that consist of GUIDs (Globally Unique Identifier) may get along without the client field.
Message 0041: INDX-type table dbtab is buffered
So called ‘INDX-type’ tables have a structure similar to that of SAP system table INDX. Such tables are normally not accessed with a SELECT statement, but with the ABAP statement IMPORT FROM DATABASE. Since this statement does not make use of any buffering, it is not reasonable to buffer an INDX-type table.
Message 0042, 0043: View dbview: First field not client, although basis table dbtab is client-specific; View dbview: Client field of basis tables dbtabs not linked using join condition
Database views based on client-dependent tables should contain the client field in the join condition. Also, the first field of the database view should be the client. Otherwise it is possible to read data cross-client with the database view, which is a security-relevant issue. The performance aspect of this issue is, that without the client field, it is possible that indexes of the tables making up the view cannot be used to optimized the data access.
Messages 0050, 0051: Language-dependent table dbtab: Language is not first field (or second after the client); Language-specific table dbtab is not buffered generically with regard to language
If a table is language-dependent, most accesses to the table will use the language field, since a user only wants to see texts in the log-on language. Therefore it is a good idea, to define the language field as the first field (or as the second field after the client), to allow for index accesses with language field plus key. Since most language-dependent tables will contain configuration data, they should be buffered. To save main memory within the buffers, such a table should not be fully buffered, but generically with respect to the language. The most important accesses, which are via the language, will then be supported by the buffer.
These are a lot of rules, but I hope they will support you to get to consistent settings for your dictionary tables and views.