QBO attempts to assist with keeping the database highly tuned as follows:
Data Retention Policy: control what data is audited, and schedule purges of data that is no longer needed
Entity View: maintain how tables participate in the Entity view
Index Tuning: maintain indexes, including removing unused indexes and creating indexes specific to a site's particular usage
Extranet Tuning: analyze a site's usage, and auto-configure triggers and object configuration to optimize performance
Data Retention Policy
All QBO tables have the option of being audited. Auditing comprises a history table to which changing data is copied, and a trigger that copies the data that is being changed. For example, the Organization table may have an OrganizationHistory trigger that copies data to an OrganizationHistory table. Every time an Organization row is updated or deleted, a copy of the row being updated or deleted (along with the user and time stamp of the change) is copied to the OrganizationHistory table. These history table can grow quite large quite rapidly, especially where there are 'portfolio updates' be passed into QBO on a daily basis.
The Data Retention Policy dashboard will display, for each installed module:
Module: the name of the module (which is the same as the database table)
History: reflects whether History information is being tracked
Not installed: there is no {Module}History table for the module
Not configured: there is a {Module}History table, but the trigger to populate it is disabled
Enabled: there is a {Module}History table, and a trigger to populate it is enabled
Purge: reflects whether there is a schedule job to purge data from the table
Not scheduled: there is no job to purge data from the table
X months, Run {some schedule}: data older than X months will be purged according to {some schedule}
e.g. 120 months, Run every 1 day(s) @ 03:00:00am will delete data more than 10 years old daily at 3 am
History purge: reflect whether there is a scheduled job to purge data from the history table
these parameters are the same as Purge, except they apply to the History table only
Best practices:
Logging tables (E.g. QueueLog, ImportLog, SecurityLog)
should not have history installed
should purge data after a reasonable auditing time frame (e.g. 3 months)
Rarely changing tables that have little business impact (e.g. MessageRecipient, HolidayInstance)
should not have history installed
High volume tables (E.g. Decision, DecisionStep, Attachment, Message)
should purge history data after a reasonable auditing time frame (e.g. 3 months)
Entity and EntityParent Views
The Entity and EntityParent Views are used by QBO to establish the relationships between rows in the system. It is the key to the Summary statement, which is leveraged by all Summary pages, and most data-driven templates (Decision, Attachment, Message). As a result the performance of the Entity View is critical to the overall performance of a QBO system.
The Entity must contains all tables for which a Summary statement will be run, which is almost all tables.
The EntityParent should contain only table which have a parent object, and can include a record more than once. For example, the Valuation table may have 'two' parents: a Property, and the Valuation.Object/ID. The EntityParent may include both for the Valuation.
When a Summary statement is run, a recursive common table expression (CTE) is used to calculate the 'ObjectTree': a table representing all ancestors and descendants of a record. Each object's Summary statement may be configured with optional parameters, including:
Ancestors: a space-delimited list of tables that should be considered as ancestors
Descendants: a space-delimited list of tables that should be considered as descendants
AncestorDepth: number of generations to calculate ancestors to (a generation is 1 pass through the recursive CTE querying the Entity view)
DescendantDepth: number of generations to calculate descendants to (a generation is 1 pass through the recursive CTE querying the EntityParent view)
The Summary statement performance can be impacted significantly by growth in tables, usually tables that participate in the EntityParent view. For example, the ImportFileQuery and ImportLog tables may grow very quickly in systems that are importing external data. Thus, as time passes, what was a performant Summary statement may become slow.
The Design > Configuration > Data Tuning > Entity View > Options > Optimize Summary command will automatically configure the Summary parameters for a given class based on data actually in the system. It scans the Entity and EntityParent views to determine which ancestor and descendant records actually exist for a given class, and sets the Ancestors and Descendants parameter to the results so that future calls to the Summary statement do not need to query tables that won't return rows anyway.
This Optimize Summary statement is certainly helpful, but manual input by an experienced power user can contribute to even more tuning. For example, a given system may hang thousands of Attachment or Ledger/LedgerItem records off an Organization. Nominally these records should be part of Organization/Summary. However, they do not have to be present in the Summary output if they are not being used by the UI or templated configurations like workflows. A power user can manually 'tune' the Organization's Summary statement, removing Attachment or Ledger, with the understanding that future UI or templated configurations won't have Attachment or Ledger data output by the Summary statement.
Index Tuning
The ConfigurationEntry/IndexList statement provides a list of indexes recommended for a given system. Recommended indexes include:
A clustered primary key index on a table's identity column
A non-clustered index on each foreign key (assuming the foreign key contains, or will contain, non-null values)
A non-clustered index on ObjectID/Object for tables that follow QBO's GenericObject pattern
A non-clustered index on each Dimension, as defined by the C# configuration file (for dashboard statistics)
A non-clustered conditional index on UpdatedDate, for calculation of deltas and use by the data listener pattern
A non-clustered index on each SmartSearch term used by the table's configuration file
Extranet Tuning
The ObjectAccess/Analysis method (and corresponding statement) will analyze current data, and recommend extranet configuration settings for a given class. Recommendations are defined as:
Recommended: this means a foreign key includes non-null data, and there are PersonAccess records exist mapping users to these foreign key values
Maybe: this means a foreign key includes non-null data, but there are no PersonAccess records mapping users to any of these foreign key values
Disable: this means a foreign key does not include any non-null data, meaning there is no possibility of users being mapped to values based on this data