Return to AboveSoft Analyzer Manual

SQL Optimization Considerations

Tips to optimize the SQL expressions

If an SQL expression has a scoring below 4, this means at least one of the tables involved in the SQL (or the only table if there's no join) cannot be accessed by an index. To improve the access for that table/s you can either:

1) Change the WHERE clause to include fields which participate in an existing index.

2) Create a new index based on the WHERE clause.

Before creating an index you should consider the following:

1) If the table is too small there may not be necessary to create an index. Particularly for a small table (e.g. a customizing table with 2000 records) that is accessed once at the beginning of a program, there might not be necessary to create such an index.

2) Do not create indexes on description fields (usually fields with 25 characters or more). One reason is because there shouldn't be any access by a description field; another good reason is that an index like that could occupy a significant space compared to the table that is indexing. In cases like this it could be better for the database engine to access the table instead of the index+table.

3) Keep in mind that creating an index improves a specific data selection; but data modification on the table becomes slower. Each time you update or insert a record in the table, the index should be updated at the same time. This also means you should consider very carefully how many additional indexes you will allow for a table.

4) Try to group as many indexes as you can by knowing the following:

If you have the following index: FieldA - FieldB - FieldC
You also have these two additional indexes:

FieldA - FieldB

  FieldA

This happens because the index is read from left to right, so a selection using FieldA will still use that index (partial index usage).

5) Try to create smaller indexes. E.g. You need to create an additional (secondary) index for a given table. You will access this table by a foreign key (a key on a different table composed of fields DOC and POS), but this foreign key is not created as an index.

You may consider creating the secondary index with fields DOC and POS; but a similar (smaller) index as good as the other could be only field DOC.

An index using field DOC is enough to shorten the sequential read to a couple of records, so it's not necessary to include the second field. The cardinality of this field is very high which means the number of returned rows is very small when selecting by this field.

6) When creating a composite index, start with the field that has the higher cardinality and end with the field that has the lower cardinality. Suppose you want to create an index for plant and material. You have 3,000 materials and 20 plants in your SAP Production system. The index should be Material - Plant (in this order).

Material has the higher cardinality since when used to select records, the number of rows returned for a single material is smaller than the number of rows returned for a single plant.