Leveraging Query Evaluation Profiles

Due to SQL and database implementation limitations queries involving DataFilter and ProcessInstanceFilter instances cannot always be directly reflected in one SQL call. As such limitations apply to various degrees in context of query complexity and database configuration, starting the Infinity Process Engine provides query evaluation profiles, allowing for more fine-grained tuning of your application performance.

By default, the query evaluation engine uses a conservative approach of separately processing DataFilter and both hierarchical ProcessInstanceFilter and ProcessDefinitionFilter criteria due to common database limitations in predictably handling high numbers of joined tables and hierarchical data, thus effectively increasing the number of database calls needed to evaluate workflow queries.

One of the results of preprocessing is an exploded set of process instance OIDs. As any database product sets certain limitations on SQL statement complexity there exist product-specific limits to the number of such OIDs allowed to be applied inline in the main SQL call. By default, the maximum size of inlined OIDs is set to 100 but can be configured by setting the server-side Infinity Process Platform property:

Carnot.Engine.Tuning.Query.InlineProcessOidThreshold

The Infinity Process Platform query evaluation engine provides an advanced evaluation profile targeting applications using data-centric queries against high volume databases. After enabling this profile by setting the server-side Infinity Process Platform property:

Carnot.Engine.Tuning.Query.EvaluationProfile=inlined

the query evaluation engine will evaluate any DataFilter criteria inline with the main SQL call. Note that this property is set to inlined by default. Also if not set or set to value default, the inlined evaluation profile is used.

While effectively reducing the total number of SQL calls needed, the main SQL statement will contain one SQL inner join operator for every distinct data ID used by the DataFilter criteria involved, thus complicating the task for the database's SQL query optimizer and increasing both memory and I/O needs for the main SQL call.

It is highly advised to profile queries involving DataFilter typical to your application for both query evaluation profiles, usually by obtaining SQL execution plans, in order to find optimal database, index and query evaluation profile configurations.