Manage resource-intensive queries
This functionality is available for Creatio version 8.0.4 and later.
Execution of resource-intensive business operations (queries) can utilize a significant amount of database resources, which often leads to lower Creatio performance for all users. Such operations include:
- Queries that filter strings by the “contains” mask. For example, standard filter that has the “Name contains %Jane%” condition in the Contacts section.
- Unfiltered, non-paginated queries generated using OData service or server-side Creatio development tools. For example, "get all contacts" query executed when you have more than 20,000 contact records.
- Queries sorted by a non-indexed column.For example, Contacts section records sorted by the custom Account country column, for which no index has been created in the database.
- Queries sorted by complex columns that contain a subquery For example, Accounts section records sorted by the column with feedback: the sum of sales for the last year for sales related to the account.
To detect such queries, Creatio uses templates stored in the Query detector lookup.
You can apply multiple actions to resource-intensive queries to reduce the load on the database significantly and free up resources for user activity and operation of other Creatio elements. These actions are listed in the Query action lookup.
- Limit query execution time. Cancel the query after the time limit specified in the “Query execution timeout” (“QueryExecutionTimeout” code) system setting passes.
- Logging. Log only query data to the Query rule apply log lookup. This helps to track the problematic queries.
- Limit the number of threads. Limit the number of threads to execute the request to the value specified in the “MaxDopQueryHint thread count” (“MaxDopHintThreadsCount” code) system setting. We recommend using a quarter to a half of total available threads, assuming one thread corresponds to one CPU core. This way, resource-intensive operations will be unable to take all available threads.
- Cancel execution. Cancel the query immediately.
We do not recommend changing the contents of the Query detector and Query action lookups. This can lead to violations of query handling rules.
Configure query handling rules
You can restrict the handling of heavy queries to improve database performance. To do this, define the type of the heaviest queries in your Creatio application and set rules that restrict their execution.
A system administrator or user who has permissions to execute the “Can manage query rules” (“CanManageQueryRules” code) system operation can configure the rules.
The basic rule structure looks as follows:
If the query goes to the <name> table, the table contains more than <n> rows, and the query matches the <query detector> template, then the <query action> action must be applied to the query.
To set up a rule:
-
Click → System Designer.
-
Click Lookups.
-
Open the Query handle rule lookup.
-
Click Add.
-
Fill out the rule parameters:
-
Enter a unique rule name in the Name parameter. Required.
-
Specify the schema (database table) to which the query must be executed in the Entity schema parameter. If no schema is specified, Creatio applies the rule to all schemas.
-
Set the minimum number of table rows required for the rule to apply in the Min row count parameter. Minimum value: 1000. Required.
-
Select the template of the query type to which to apply the rule in the Query Detector parameter. Available values are stored in the Query Detector lookup. Required.
-
Select the action to apply to the query in the Query Action parameter. Available values are stored in the [Query Action] lookup. Required.
noteSystem checkbox determines the rule editability and priority. System rules can be configured only by cloud administrators in accordance with the cloud policies and restrictions. These rules are mandatory and cannot be modified or deleted by Creatio users.
-
Specify the rule availability in the Active — determines whether the rule is used or not.
-
Specify whether to log the Stack trace of the call of the operation that executes the query to which the rule was applied in the Log stack trace parameter. Logs are saved to the Query rule apply log lookup.
-
Specify whether to log the query execution time in the Log query execution time parameter.
-
-
Save the changes.
-
Repeat steps 4–6 for other needed rules.
Execute rules
Rule priorities are based on the detector, system character, schema (table), and number of table rows. When the query is executed, Creatio checks the following:
- query detectors used
- entity schema and row number
- whether System checkbox is set
If multiple rules apply to the same query detector and entity schema, Creatio applies only one system and one regular rule that match the most closely.
The examples below explain how rules affect the execution of resource-intensive queries. In the examples, the following set of rules is configured in Creatio.
Entity schema | Min number of rows | Query detector | Query action | Is System | Is Active |
---|---|---|---|---|---|
Like 1 rule | |||||
Not set | 20000 | Text filter detector by pattern “%...” | Limit query execution time | No | Yes |
Like 2 rule | |||||
Not set | 500000 | Text filter detector by pattern “%...” | Cancel execution | No | Yes |
Index 1 rule | |||||
Not set | 10000 | Not indexed column sorting detector | Limit query execution time | No | Yes |
Index 2 rule | |||||
Not set | 500000 | Not indexed column sorting detector | Cancel execution | No | Yes |
Activity Like 1 rule | |||||
Activity | 20000 | Text filter detector by pattern “%...” | Limit query execution time | No | Yes |
Activity Like rule | |||||
Activity | 1000000 | Text filter detector by pattern “%...” | Cancel execution | No | Yes |
Activity Like sys rule | |||||
Activity | 10000 | Text filter detector by pattern “%...” | Limit the number of threads | Yes | Yes |
Activity no fil no pag sys rule | |||||
Activity | 10000 | Not indexed column sorting detector | Limit the number of threads | Yes | Yes |
The user searches in the Contacts section by filtering “Full name contains <Jane>
” and sorting by the non-indexed column Date of birth. The “Contacts” table has 100,000 entries.
The rule selection logic works as follows:
- The query fits two detectors: “Text filter detector by pattern "%..." and “Not indexed column sorting detector.”
- “Contact” entity schema has no specific rules.
- Fitting rules have no system rules.
As a result, the most suitable rule will be executed for each query detector.
Entity schema | Min number of rows | Query detector | Query action | Is System | Is Active |
---|---|---|---|---|---|
Like 1 rule | |||||
Not set | 20000 | Text filter detector by pattern “%...” | Limit query execution time | No | Yes |
Index 1 rule | |||||
Not set | 10000 | Not indexed column sorting detector | Limit query execution time | No | Yes |
The user searches in the Activities section using the filter “Name contains <account>
” and sorting by the non-indexed Created Date column. The “Activity” table has 120,000 entries.
The rule selection logic works as follows:
- The query fits two detectors: “Text filter detector by pattern "%..." and “Not indexed column sorting detector”.
- “Activity” entity schema has specific rules.
- Fitting rules include system rules.
As a result, the most suitable system rule and common rule will be executed for “Text filter detector by pattern "%..." query detector. Only the most suitable system rule will be executed for “Not indexed column sorting detector” because no common rules for this query detector were configured.
Entity schema | Min number of rows | Query detector | Query action | Is System | Is Active |
---|---|---|---|---|---|
Activity Like 1 rule | |||||
Activity | 20000 | Text filter detector by pattern “%...” | Limit query execution time | No | Yes |
Activity Like sys rule | |||||
Activity | 10000 | Text filter detector by pattern “%...” | Limit the number of threads | Yes | Yes |
Activity no fil no pag sys rule | |||||
Activity | 10000 | Not indexed column sorting detector | Limit the number of threads | Yes | Yes |
Analyze rules execution
Creatio stores the logged query execution data in the Query rule apply log lookup. For each of the configured rules, only the first operation for each of the users during the day is logged. This enables the administrator to identify Creatio bottlenecks and make appropriate changes based on the rules that are used most often.
In the log you can find the following information:
- Schema name. The root schema (database table) to which the request is made.
- User login. The user who initiated the request.
- Last detection date. Date of the last rule triggering on request.
- Query detector. Query type template for which the rule was applied.
- Query action. The action that was applied to the request.
- Message. Information that additionally reveals the reasons for triggering the rules: the number of records in the table, as well as by what criterion the trigger was triggered (for example, which heavy filter for which column).
- Recommendation. A recommendation to the system administrator how to reduce the impact of the query (for example, depending on the detector, add an index to the column, clear irrelevant/historical data in the table, strengthen the filter, etc.).
- Execution time, sec. A query execution time in database (to log this parameter, column Log query execution time of Query handle rule must be set up to “Yes”).
To view the SQL text and stack trace of the query, you can use the Show SQL text and Show stack trace buttons, which are available after clicking on the log entry. This information may be needed to analyze the request in more detail and determine the functionality that generated it. Stack trace logging is off by default. But you can turn it on in the Query handle rule lookup.