QBO attempts to assist with keeping the database highly tuned as follows:
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:
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:
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.
The ConfigurationEntry/IndexList statement provides a list of indexes recommended for a given system. Recommended indexes include:
The ObjectAccess/Analysis method (and corresponding statement) will analyze current data, and recommend extranet configuration settings for a given class. Recommendations are defined as: