Using Database Indexes

Data Objects (DO) provide the facility to add indexes on one or more attributes contained within the same Data Object node for the purpose of performance optimization.

Conceptually a DO represents a mobilized business object, however the client side implementation is actually a table within a database along with Java code representing actions that can be taken on the object. Using this database table as a basis you can then understand that a Data Object index is the equivalent of adding an index to an SQL database table as this is in fact the underlying technology of how the DOs are being stored on the handheld device.

Due to the nature of the implementation of the DO indexes, they only provide a performance increase when the selectivity of the data contained in the index key attribute is high. For example if you create an index on a surname attribute in an address book where most people have the surname "Smith" then the selectivity of the index will be very low and will not yield a performance increase.

The best index your Data Objects can have is when the attribute used as a key field is unique. This yields a highly selective and optimal index. The DO index creation wizard supports the adding of a "unique" constraint to your index. This will ensure that all data added to the DO index attributes will be unique in nature.

When you add indexes to DOs, it produces a performance hit when lots of data are inserted into the Data Object. When data is inserted the DO index has to be maintained and updated by the client database storage engine.

SYNCKEY and PSYNCKEY are the Indices created by default. Most of the application search takes place on an attribute which is used by the end user or backend key fields. For Example, searching a business partner is based on a Partner Name or Partner ID. In this case it is advisable to set the Partner Name or Partner ID as Indices.

You can use indexes for the following scenarios:

• SELECTs with an ORDER BY clause to avoid temporary table creation if the column has high selectivity

• Multi-table SELECTs with JOIN and/or projection conditions

• Conditions connected with AND operator

• Filter on columns with a high percentage of distinct values (high selectivity) compared to the overall number of rows in the table

You have created a service component and imported DOs from the Data Orchestration engine.

1. From the Service Explorer, expand the Service Component node.

2. Expand the Data Object node, and choose the required data object.

3. Navigate to the Indexes tab page.

4. Choose Add Index .

5. Enter an Index Name .

6. If you want the index attribute(s) to be unique then select the Index is unique checkbox.

7. From the Available attributes area, select the attributes you want to use in the index and choose >.

8. Save your changes.

You have created a new index.

Performance of certain query operations during runtime will be improved, however the performance increase will depend on the query structure and indexes used.