Materializations

A calculated property can be materialized. In this case, the property will be stored in the database permanently and automatically updated when the data on which this property depends is changed. At the same time, when reading the values of the materialized property in a change session that has not changed the data it depends on, these values will be read directly from the database, as if the property was data (and not calculated every time). Accordingly, all data properties are materialized by definition.

A property can be materialized if and only if for it there is a finite number of object collections for which the value of this property is not NULL (that is, the iteration operation for all of its non-NULL values is correct).

A property that is not materialized is computed inside every query that reads it: a property created by a set operation — grouping, partitioning, recursion — becomes a subquery of that query. An abstract property that is not materialized is computed in the same way, from the definitions of the implementations that may apply together with the selection among them, so the query that reads it grows with their number and size - for example, a grouping implemented separately for each of several descendant classes. When such a property is read inside another set operation, its subquery is included in the text of the outer query - once for every place and every form in which it is read there (for example, its new and its previous values separately while changes are being computed) - so the text of one query grows with the number of such places and the size of the nested definitions. The same holds for the queries that update the stored values of a materialized property when the data it depends on changes: the definitions of the non-materialized properties it reads — for a materialized grouping, its grouping keys included — are expanded into the text of the update query, which therefore grows with the size of those definitions and can exceed the limit below even when the change affects a few object collections. The length of the text of one query is limited by the working parameter queryLengthLimit (2000000 characters by default): a longer query is rejected by the application server before it is executed (the TOO LONG QUERY error), whatever the number of rows it would return, and is not retried. Whether the changes of the intermediate properties such an update reads are first written into temporary tables, which can shorten the update query, is decided when the query is built — by the complexity of the definitions and the statistics of the changed data — so the same change can exceed the limit on one server and fit on another, or on the same server at another time. The limit is checked when the query is executed, not when the modules are loaded: even when the modules have loaded without errors, with the dryRun check included, applying a change of the data the materialized property depends on can fail with the TOO LONG QUERY error. Materializing the inner property replaces its subquery with a read of the stored values. When the outer query reads such a grouping for a part of its groups only — for example, for the keys written into a local property by an import — the platform moves that restriction inside the subquery when, by the statistics, the restricted subquery is estimated as cheaper. A small number of distinct restricting values can make that move cheaper, but the ratio between that number and the number of groups does not determine the plan by itself. When a local property is filled from memory, an import included, the platform counts the actual rows and the distinct values of each key and property column, then rounds these counts for query planning. If the restriction is not moved inside, it is applied to the result of the grouping, and reading the grouping into a local property beforehand does not, by itself, avoid computing all groups. An abstract property can be materialized like any other calculated property: its values are stored in one field of its table, and when that table is keyed by the abstract class the field holds the values for the objects of all its descendants.

A query is compiled - its text is built from the definitions of the properties it reads - when the cache of compiled queries (see working parameters) holds no query for it, which is the case at its first execution after the server start. With a large definition this compilation itself can take noticeable time: the first read after the start can be slow, while the repeated reads reuse the compiled query from the cache. For a materialized property the query reads the stored field instead of the definition (in a session that has not changed the data it depends on, as above), so the definition is not expanded into that query.

The stored values of a materialized property can be recalculated — recomputed from scratch from the property's definition. This is needed when those values have diverged from the definition: after the definition changes — for an abstract property, after one of its implementations is changed or added — or after a direct data fix in the database. A server start does not recompute the values of a property only because its definition changed: at start the platform recalculates the properties materialized for the first time, whose field has just been created, and the materialized properties that depend on static objects added at this start — those in full, existing stored values included. Until the property is recalculated, its stored values therefore stay inconsistent with the changed definition: a change of the data the property depends on updates only the affected object collections, and an aggregate can be updated from its stored value — a sum, for example, by adding the change to it — so even those values are not necessarily brought to the new definition.

Recalculation

Recalculating a materialized property compares its stored values with its definition and writes back the ones that differ. Several working parameters shape how that is done.

useRecalculateClassesInsteadOfInconsisentExpr (true by default) first recalculates the classes of the table the property is stored in, and only then compares the values, so the comparison can use the ordinary expression of the property. With it off the classes are left alone and the comparison uses an expression that tolerates inconsistent class data, which adds joins and needs more memory. Recalculating the classes is the more invasive of the two, since it rewrites the class data of the whole table. When a recalculation is asked for without the classes, the parameter does not apply.

recalculateMaterializationsMixedSerializable (false by default) changes how a recalculation that runs in its own transaction holds its isolation level: the mismatched rows are collected first, outside the transaction, and only writing them back is done at the strictest level. It costs an extra temporary table and a second query, and it does nothing for a recalculation the administrator asked to run inside a single shared transaction.

maxRecalculateTime (300000 milliseconds) is not a timeout - nothing is interrupted by it. An operation that took longer than it is added to the report the service action returns, so that the slow tables and properties can be seen. 0 puts practically every operation into it, since the comparison is strict.

groupByTables (true by default) groups the properties being recalculated by the table they are stored in: in a multi-threaded recalculation the properties of one table run one after another rather than at the same time, so they do not wait on each other's locks. It is also what updates, at server start, the statistics of the columns that the synchronization of the database schema has just created or moved. With it off that statistics update is skipped entirely, and queries over those columns keep their poor plans until the statistics are recalculated by other means, so it is better left on.

Language

To materialize a property, use the MATERIALIZED option in the property options. To recalculate the stored values of a materialized property, use the RECALCULATE operator.

Examples

sum = GROUP SUM sum(OrderDetail od) BY order(od) MATERIALIZED;
date(OrderDetail od) = date(order(od)) MATERIALIZED;

 // such a property cannot be materialized, since it is not NULL for an infinite number of dates
lastDate(Customer customer, DATE date) = GROUP LAST date(Order order) IF customer(order) = customer AND date(order) < date ORDER order;