Analyze memos
In object management, each user can set, via the menu Selection / Selection, filters used to select from the left list only part of a table. Such selections can be saved as a memo, and therefore reused as required.
This interesting functionality is nevertheless the source of potential performance problems when the tables as well as the filters are very large (for example, containing several thousand lines). Additionally, the memos can be standard (i.e. loaded when the user starts a function), and global (i.e. shared by all users).
The analysis function is used to detect potential performance problems taking into account the setup of memos by the users in a folder. Warning, it only gives indications, and it can in certain cases reveal problems that don't exist. For example, a memo for which no discriminating index is found for the user can complete a filter defined elsewhere in the standard process logic.
It is nevertheless prudent to verify, for each log file line found this way, the error message relevance, by taking corrective measures. These measures can be of two types :
- deletion of the corresponding memo or warning the user
- adding an optimization index if this appears appropriate.
In order to obtain this result, the process reads all the memo files present in a folder, matches the criteria used to the different indexes existing in the table (this includes the optimization index), and runs a diagnostic taking into account the number of lines present in the table.
It should be noted that two parameters, described below, can be used to control the messages created by the users But these memos are only checked on their creation. However, memos considered efficient on creation can very easily become less so a few months later, if the database volume leads to a longer execution time. It is for this reason that it is useful to launch this utility even if the two parameters described below are correctly assigned.
Prerequisite
Refer to documentation Implementation
Screen management
Entry screen
Three values (in number of lines) are defined characterising the tables in which the memo test will run.
When the function execution is finished, a log file is generated containing two sections. The first part is a list of problems found, memo files sorted in alphabetic order and numbered. The log file provides information in the following form :
- a first line of the header detailing the memo name (NAME), whether it is a local or global memo, the user concerned (UUUUU), and a table in which the memo is located (XXXXXX). The problematic memos are numbered ((NNN field), those that give the following line (local can be replaced by global) :
NNN local memo UUUUU.NAME for table XXXXXX (table title)
- one or several lines explaining the performance problems encountered in this memo. The grids below give the messages likely to be encountered:
|
MESSAGE |
DEFINITION |
|
*** WARN (MMMM) *** DESCRIPTION |
Performance problem : the table has MMMM lines. |
|
*** PERF (MMMM) *** DESCRIPTION |
Serious performance problem : the table has MMMM lines. |
|
*** CRIT (MMMM) *** DESCRIPTION |
Critical performance problem : the table has MMMM lines. |
|
FIELD DESCRIPTION |
Explanation |
|
No index for the filter on the field(s) FIELD1 FIELD2 … : Performance problem |
Taking into account the specified filters no appropriate index exists. If the memo is useful and used frequently, it will be necessary to consider an optimization index. |
|
The left list sort key (KEY1) is different to the filter key (KEY2). |
The database itself serves as a primary index (KEY2) to filter the data, then the data is sorted according to the KEY1 index in order to present the left list. This can be a performance problem if the index serving the filtering is not very selective (a large number of lines before being sorted). |
|
‘Different’ operator on the field FIELD1 ‘Like’ operator on the field FIELD1 |
These two operators prevent the database using the index in a simple fashion on the value ranges; the performances can therefore be poor. |
|
'Or' operator between two conditions |
The selection implies one or more conditions separated by 'or'. This type of request is in general pretty heavy in terms of performance. |
|
Selection on an expression : expression |
This type of selection is not analyzed and must therefore be verified to know if a potential performance problems exits. |
|
Several tables in the memo verify the request |
The selections are made on the joins. This type of request cannot be verified automatically by the utility : a manual verification is necessary to know if a performance problem could exist. |
The second part of the trace gives a hierarchical list of the previous problems (one line per problem). Found here is the problem number, the name of the memo in form of UUUUUU.NAME/TABLE The sort order is as follows :
- the standard memos are ranked above the non standard memos.
- the global memos are ranked above the local memos.
- when the first two criteria are equal, the sort is in descending line number order in the table.
This makes it possible to focus on the memos likely to provoke the most performance problems.
Test options
| Critical size (field NBLIGBIG) |
|
This size (in number of lines in the table) define the threshold above which an index problem in the memo is considered as critical from the point of view of the performances. |
| Size performance (field NBLIGTB) |
|
This size (in number of lines in the table) define the threshold above which an index problem in the memo is considered as serious from the point of view of the performances. |
| Size warning (field NBLIGWARN) |
|
This size (in number of lines in the table) define the threshold below which an index problem in the memo is not considered as posing a problem from the point of view of the performances. From this threshold, a warning message will be displayed (if the following thresholds are not exceeded). |