Query join and complexity size limits

Query join and complexity size limits

Query join and complexity size limits

The ServiceNowITSA Suite platform uses a relational
database to store data. Retrieving data can involve multiple joins to create a single result set.
While these joins are usually simple, in certain cases the system may issue very large joins to
bring together large numbers (>20) of tables.

Database engines normally handle multiple joins quite well. The relational model assumes joins are cheap and efficient, and this is usually true.
Rarely, however, exceedingly large joins may cause a database performance issue.

To mitigate this potential complexity, use the following property to limit join complexity and
size. Navigate to System Properties > System to find the property with the
description, Max number of database joins per query. Smaller values cause the system
to issue a larger number of less complex queries. Larger values reduce the number of queries at
the cost of additional complexity per query. In the absence of known database issues stemming
from large join counts, this property should remain unchanged.

Figure 1. Query complexity limits

This property specifies the maximum number of joins the system prefers to do. In certain edge
cases, more complex queries might need to be issued, but generally no more than this number of
joins will be issued. e.g., a value of 10 will result in no more than 10 tables being joined
together in any given query.

The system must compensate for the fewer joins by issuing more queries to retrieve necessary data, so tuning this number down will result in more queries being sent
to the database. In most cases, tuning this parameter is counterproductive.

Note: In the absence of known database issues stemming from large join counts, this parameter should
remain unchanged.