Performance of dbdicts and tables

The definitions of dbdicts in Service Manager have a huge impact to system performance and response time. The following are the aspects in a dbdict definition:

• Mapping of the dbdict to tables in the relational database management system (RDBMS)
• Indexes on the tables in the RDBMS
• The number of fields in the dbdict. This increases the size of messages exchanged between the client and server.

The mapping of the dbdict to tables defines how many tables the dbdict is mapped to, and whether the fields are mapped to SQL column types interpretable by the RDBMS (such as VARCHAR, NUMBER, and FLOAT) or not (such as IMAGE, and CLOB).

Using non-interpretable fields may improve the performance mildly for reading and writing records, and they can be used neither by the index on a table, nor in the WHERE clause of a SQL SELECT statement. Service Manager relaxes queries that use these fields by replacing this clause with “(1=1)” and re-selects among the records in its own memory. Using non-interpretable fields in queries therefore causes the RDBMS to fetch more records and send them over the network, and causes Service Manager to load more records into its own memory for reselection.

Dbdicts support array and structure-type fields. These fields cannot be natively mapped to SQL column types, and must be mapped either to non-interpretable column types or as tables for the array elements using the primary key (or the first unique key) fields and the RECORD_NUMBER field (which contains the position of each element in the array).

Array fields mapped as tables of interpretable column types can be used efficiently in queries , because Service Manager is then translating the queries into a SELECT statement on the join of multiple tables.

As the database operations are executed at the RDBMS, dbdict keys have no direct impact on database performance. Their relevance is as follows:

• The primary (first unique) key is relevant as the record identifier during query processing: Service Manager first selects a list of key values that match the query, before selecting the fields displayed in the record list view or all fields for the record displayed in the detail format.
• The primary (first unique) key is relevant for the mapping of array fields.
• All dbdict keys are translated into indexes on the RDBMS when a dbdict is initially mapped to tables or remapped. Because remapping means creating new tables according to the modified mapping and replacing the existing tables, all modifications of the tables on the RDBMS side are not maintained.
• When a new key is created in the dbdict, an index is automatically created on the table.

Indexing all fields used in queries that are executed frequently by the out-of-box implementation or a tailored implementation is required for an efficient system. User-defined queries that are executed from search formats, view definitions or reports typically cannot be all indexed. Training and highlighting indexed fields in a search format (that is, by using bold font for field labels) will enable the user to formulate queries efficiently and achieve short response times.