Considerations When Using DataConnection
Consider the following when using DataConnection with the Business Rule Engine.
Use primary keys
When there is a primary key, the equality of two rows is determined by whether the rows have the same primary key, rather than by object comparison. If the rows are determined to be the same, only one copy is retained in memory, and the other is released. This results in less memory consumption.
When a DataConnection is asserted into the rule engine for the first time, the engine always tries to locate its primary key information from its schema. If a primary key exists, primary key information is then retrieved and used in all subsequent evaluations.
Note
A primary key is mandatory if changes need to be made to the database.
Provide a running transaction to the DataConnection whenever possible
Without a transaction, each query and update on the DataConnection initiates its own local transaction, and different queries might return different results in different parts of rule evaluations. Users may experience inconsistent behavior if there are changes in the underlying database table.
Although you can use a DataConnection without providing a transaction when the table does not change over time, it is recommended that a transaction be used even when the DataConnection is only being used for read operations.
However, a transaction should always be used when updating data.
Number of queries may grow linearly
As queries against the DataConnection are parameterized by other joined objects, the number of queries executed against the DataConnection corresponds directly to the number of joining objects reaching the DataConnection. Therefore, if the number of joining objects reaching the DataConnection object grows linearly, the number of queries against the DataConnection will grow linearly as well. Currently, there is no optimization in place to reduce the number of queries.
An example of this is the rule:
IF A.x = 7 AND DC.y = A.y
THEN
A represents an ObjectBinding, DC represents a DataConnection, and x and y represent attributes of A and DC.
For every instance of A that passes the test (x = 7), a query is generated by using the DataConnection. If there are many matching instances, the same number of queries results.
Use OR conditions with caution
If the rule uses only conjunctive (AND) conditions, tests and queries will be executed as early as possible, so instances of objects passing through will be reduced. As a result, the number of queries against the subsequent DataConnection will be reduced proportionally. If disjunctive (OR) conditions and a DataConnection are used together in a rule, all condition evaluations will be pushed to the final query. If more than one DataConnection is used in a rule, all queries except the last one will effectively become a Select-ALL query statement.
In general, it is better to split any rule with an OR condition into two or more discrete rules, because the use of OR conditions will decrease performance compared to the definition of more atomic rules. This is true whether DataConnections are used or not.
You may also consider using separate rules that consist only of conjunctive conditions instead of one rule with OR conditions. With OR conditions, the number of queries grows at the speed of multiplication of instances of all joining objects. This is shown in the following example.
IF (A.x ==7 OR A.x == 8) AND DC.y == A.y
THEN DC.z = 10
In this example, A represents an ObjectBinding; DC represents a DataConnection, and x, y, and z represent attributes of A and DC. If A has 100 instances, and x is 1 in the first object, 2 in the second object, through 100 in the 100th object, 100 queries have to run against the DataConnection.
It is better to rewrite the preceding rule by splitting it in to two rules.
Rule 1
IF A.x =7 AND DC.y = A.y
THEN DC.z = 10
Rule 2
IF A.x = 8 AND DC.y = A.y
THEN DC.z = 10
SQL does not support some predicates and functions
Some predicates and functions that the rule engine supports are not supported by SQL. If these unsupported predicates and functions are used in the rule conditions, it cannot be incorporated into the query. The following terms cannot be optimized as an SQL query:
Some of the engine built-in functions have no equivalent in an SQL query. These are Power, FindFirst, and FindAll. Using a DataConnection column as one or more of their arguments cannot be optimized as part of a query; for example, Power( 2, dc.Column1).
Built-in predicate Match that uses a DataConnection column as its regular expression argument (the first argument). For example, Match("abc*", dc1.Column2) is valid, but Match(dc1.Column1, dc1.Column2) cannot be translated.
Using a user function that has a DataConnection column as one of its parameters. For example, c1.M(dc.Column1) cannot be optimized because the user function cannot execute on the database server, but must be executed by the Business Rule Engine.
User functions that represent set operations on the DataConnection; for example, dc.Column1(5).
Functions that use an ObjectReference to the DataConnection; for example, ObjecRef(dc).
If one or more of a function's arguments is untranslatable, the function call becomes untranslatable; for example, Add(c1.M(dc.Column1), 5).