Return to AboveSoft Analyzer Manual

SQL Analyses

AboveSoft Analyzer perform their SQL Analyses on the following basis:

SQL Analyses scope:

The following "Open SQL" SELECT clauses are considered:

Index Analyses are performed on the following table types:

The buffered tables are dismissed for the analyses since these tables are cached in memory and the access time is not significant. This kind of table appears as "ignored" in the SQL report; except if those tables appear in a Join relationship (in that case the buffer cannot be used).

AboveSoft Analyzer DOES NOT SUPPORT:

Additional information:

SQL SELECT expressions that involve access to views instead of tables cannot be analyzed for index utilization. If a view is used, the best case -using a full index- will be assumed.

Since the client field (usually MANDT) is not selective, it will not be considered as a partial index in use.

SQL Analyses rate

Section 1

Evaluation item
Scoring
Asterisk is not used
3
Asterisk is used Table's record length <= 250
2
Table's record length <= 500
1
Table's record length > 500
0

Section 2

Evaluation item
Scoring
At least one full index can be used
7

At least one partial index (not considering client) can be used

4

Table scoring = Section 1 + Section 2

The weight for each table is the "Size category" + 1 (this always consider every table). Buffered tables are dismissed, except if they intervene in a JOIN relationship (since the buffer is bypassed).

SQL Weight = Weight_1 + Weight_2 + Weight_3 + ...
SQL Scoring = (Table_1 * Weight_1 + Table_2 * Weight_2 +...) / Weight_total
Report Weight = Weight_1 + Weight_2 + Weight_3 + ... (each TABLE weight)
Report Scoring = (Table_1 * Weight_1 + Table_2 * Weight_2 +...) / Weight_total

Every SQL is comparable to another SQL (with the score 0~10 without considering weight); however, if what is being analyzed is a group of programs, each program will influence the result more if more tables are used in it.

Scoring Interpretation for a single table (or a single table SQL clause)

10
Perfect. A full index can be used and the asterisk is not present.
8 - 9
Very good. A full index can be used but the asterisk is present. It can be improved a little by removing the asterisk.
7
Fine. Here there are two interpretations:
1) A full index can be used and the asterisk is present. Better modify to remove asterisk since it's moving a lot of data for each record it selects.
2) A partial index can be used and the asterisk is not present. You should evaluate how useful the index is.
4 - 6
Bad. Access can always be improved. Asterisk is always present and a full index cannot be used. A partial index can be used but you should evaluate how useful it is.
0 - 3
Very bad. No index can be used. Access must be improved or it may represent a problem.

The scoring of an SQL which handles more than one table is the average of each table scoring. As a general rule an SQL scoring 7 or more (green) is considered OK. You will always be able to improve SQLs scoring less than 7.

IMPORTANT: SQL Scoring considerations

The SQL scoring represents the expected performance for the given SQL expressions; however the SQL expressions may not behave as expected under certain circumstances:

SQL Optimizer

This feature analyses an SQL and calculates the current expected performance. It also provides some tips on how to improve the SQL clause and which would be the expected performance if those tips were applied.

When there is more than one table in an SQL expression, the SQL Optimizer internally switches the order of tables to find out which combination performs better. When more than one combination performs equal it prioritizes the one that starts with the smaller table. It's also able to change the WHERE clause to filter the selection closer to the first table found in an SQL expression (e.g.: An SQL from MKPF inner join MSEG; if the WHERE indicates MSEG~MJAHR = <some_year> and MSEG~MBLNR = <some_document> the application will indicate that is better to filter those values on MKPF~MJAHR and MKPF~MBLNR).

The SQL Optimizer is also based on a Nested Loop database plan for the JOIN clauses. The SQL Scoring considerations (above) also apply to the SQL Optimizer.

To access the SQL Optimizer just click on the links SQL Optimizer for this SQL expression in the SQL Analyses reports, or also from the menu Action > SQL Optimizer.