Performance Considerations

This chapter presents performance considerations.

Knowing the Cost of DataOrder

Every DataOrder criterion introduced will require application of one SQL outer join operator. Depending on your database implementation, efficiency of outer join evaluation may be reduced, i.e. as of inabilities to leverage indexes to avoid sorting. One known case is Oracle 8i due to query optimizer limitations regarding the Oracle-specific outer join syntax. If your configuration permits (means you use Oracle 9i or later), consider setting the server-side property

AuditTrailDatabase.Type=ORACLE

to allow for use of the better performing ANSI join syntax.

It is highly advised to profile queries involving DataOrder usually by obtaining SQL execution plans, in order to find optimal database and index configurations.

If tuning alone does not lead to sufficient query performance, consider optimizing queries involving multiple DataOrder criteria by introducing an artificial workflow data containing the length-padded concatenation of the data defining the requested order, thus effectively reducing the number of DataFilter criteria needed.

Tuning Dependent Object Prefetch

Dependent objects of queried items (i.e. data values used in Descriptors) are loaded in batches to reduce the number of SQL calls needed. The maximum batch size is set to 400 by default. Depending on your application you may increase this number to in case of few Descriptors but large result sets, or decrease this number in case of many Descriptors (yielding too complex SQL statement errors), by setting the server-side property:

Carnot.Engine.Tuning.Query.DescriptorPrefetchBatchSize

Prefetching Descriptors with Data Clusters

Data values for descriptors can be prefetched on existing data clusters to improve performance. To enable this prefetch functionality, set the following property to true:

Infinity.Engine.Tuning.Query.DescriptorPrefetchUseDataCluster

The default value is false.

Know your Index Needs

The default Infinity audit trail contains a basic set of indexes, containing a compromise between batch processing and performance in interactive use. As added indexes always involve the tradeoff between increased search performance vs. increased maintenance costs there can't be a perfect set of stock indexes for every application. Thus it is highly recommended to make database and statement monitoring an integral development and deployment step in order to match your application's specific performance needs.