Optimizing the Runtime of an Offline Dataset

Simplifying the Dataset Definition

A dataset determines all data in an info area that either is included in one of the filters specified in the dataset or is linked to at least one of the specified child records.

These conditions are included in a single SQL statement. In addition, the conditions and dependencies defined in child datasets as well as the access rights defined for all involved info areas are also included in this statement.

Combining child datasets and/or access rights definitions often results in complex database requests (which take a long time to execute).

Using filters instead of dependencies result in simpler SQL statements. Depending on the data volume you can also export all records the user is only allowed to view.

Splitting the Dataset Definition

More than one dataset can be defined for an info area and each definition is treated as an independent request. If you define complex filters (linked using "OR") or multiple child info areas (that are also linked using "OR"), each setting can be moved to a separate dataset.

The same record can be returned by multiple datasets, but avoid large overlaps as this data needs to be transferred over the network multiple times to the iPad.

If a dataset is split into multiple datasets, child datasets are only synchronized with the original dataset. For example, if KP is synchronized for all FI, and the FI dataset is split into FI, FI2 and FI3, both FI2 and FI3 need to be referenced by the KP dataset, or separate KP2 and KP3 datasets need to be defined for FI2 and FI3 to ensure that all KP records are transferred for all FI records.

Simplifying Access Rights

Only a user’s view access rights are relevant for the synchronization. Bottom-up access rights (where the access rights depend on sub-tables) are performance-intensive, e.g. "companies with a linked SB record where the user is entered as a rep" or "companies that had contact with the user in the last 2 years". Multiple combinations of such bottom-up access rights result need more resources to process.

Try the following approaches (which can be combined):

• Simplification: Edit the access rights so that they are easier to evaluate, the trade-off being a semantic change to the access rights definition. Usually, data exists that the user may not view (set A), data that the user may view but is not interested in (set B) and data that the user may view and is interested in (set C). In this case, you can try to simplify the access rights for set B. The user still cannot view set A while having a complete full view of set C.
• Pre-triggering: Access rights can also be simplified by using triggers that create the necessary access information upon saving the record. In an extreme case, a record could exist in a child info area for each FI record and the user is allowed to view the record. In this case, reduce the access rights to a single bottom-up condition. However, triggers need to be used to ensure that these child info areas are kept up to date.

Measures that simplify access rights not only improve the performance of CRM.pad's synchronization but also the performance of queries in all applications that access the Aurea CRM database.

Optimizing the Database

Apply the following:

• Optimizing the database indexes (either manually or with a database tool)
• Maintenance (keeping database statistics up-to-date, avoiding the fragmentation of data files)