Database Performance

As the acces to the database can be defined in many different ways and many different aspects might have an influence onto the database performance the below written article has the intention to summarize different design and usage aspects of the setup of a database and the usage. For general aspects perhaps Performance Analysis might help.

There are some base ideas and concepts defined in TradeDesign which should be kept in mind when defining a table.

As TradeDesign uses a database transaction independent locking mechanism, TradeDesign needs to know the fields which shall identify the entry (or multiple entries) which shall be used to identify the locked entry. As close to all table are using a column INR to identify the different entries the lockfield of those table is exactly the INR. In some rare cases other fields might define the lock key. It is a good idea but no hard need to have a unique lock key. If the lock key is not unique, the whole set of entries will be locked in case of a lock.

To be able to use different databases and have a fast access to a table entry, the database design of our table uses the column INR with an 8 character id (take from a counter per table) to uniquely identify every entry. It is the general idea that the INR is not visible to the normal user, with very few exceptions, where a running counter is needed. Due to this invisible id, the visible key might be changed without affecting the database structure and integrity.

In our applications there should be no link between table by external visible fields. Linking of table entries should always be implemented by using the INR of the target table. The linking table should hold either a pointer field named by the target table and the postfix INR ( LEDINR or PTYINR ) or a pair of fields with the target table and the target INR (typically OBJTYP and OBJINR).

Indexes are setup in the database and have the intention to access the individual entries quickly without searching through the whole table.

As all links shall be setup by using INR fields, each table with an INR should hold an index which is made up on the INR column. This xxx_INR index is typically a unique index.

For table which are accessed via quicksearch, the search field GETFLDNRM should be setup as an index.

Table which are setup with multientity selector fields ETYEXTKEY or ETGEXTKEY an index for those columns should typically be defined.

In table where there is a pair of fields OBJTYP and OBJINR is defined and used to link the entries to other database entries and those fields are used to select the matching entries, typically a not unique index xxx_OBJINR is defined consisting of the fields OBJTYP and OBJINR.

If there are other typical conditions used to select the matching entries for each of the conditions it might make sense to define an additional index.

Thus a typical table like the PTY table holds at least the following indexes

Take as sample the table PTY which holds the party data.

The field definition using some links are visible below. The fields HBKINR and HEQINR are links onto other PTY entries which are the housebank or the headquarter of the PTY entry.

The index definition shows the typically needed indexes (PTY_INR, PTY_GETFLDNRM, PTY_ETGEXTKEY) together with additional special indexes with often used additional search fields.

The lock field definition is the usuall lock field INR.

When there are database performance issues those might be differentiated into

1. Infrastructur (load to the server or network, network latency)
2. slow implementation within application
3. slow database setup