Complex Select queries
Complex database queries can put a 100% load on the resources of the database server for a long time. This obstructs the workflow of other users or makes it impossible.
The types of complex queries are as follows:
- suboptimal queries in dynamic folders, dashboard blocks
- complex analytical selections in dashboard blocks
You can execute complex Select
queries using the following means:
- a dedicated query pool (available for Microsoft SQL Server Enterprise Edition)
- a read-only replica
These ways to execute complex Select
queries let you:
- limit resources the database server dedicates to complex
Select
query processing - reduce the impact of complex
Select
query processing on the workflow of other users and the operation of other parts of Creatio
Dedicated query pool
The purpose of a dedicated query pool is to process complex Select
queries that are not part of the transaction and are externalized to a dedicated pool.
1. Set up the connection of a dedicated query pool
Microsoft SQL Server lets you limit resource allocation using the built-in Resource Governor tool. The Resource Governor rates connections based on the information about the connection, not a particular query. The impact of the tool is barely noticeable on an idle server and short queries. The effect of the Resource Governor is observable when the database server has a 100% load and a complex query takes a long time to execute.
To set up the connection of a dedicated query pool, open the ConnectionStrings.config
configuration file and add the _Limited
suffix to the App
or Application Name
property. This setting enables a special connection that divides the queries into simple and potentially complex.
The connection types are as follows:
-
If you do not specify a value for the
App
property in the connection string of theConnectionStrings.config
configuration file, Creatio will use default connections. The default generic connection is ".Net SqlClient DataProvider," the default connection of a dedicated query pool is ".Net SqlClient DataProvider_Limited." -
If you specify "creatio" for the
App
property in the connection string of theConnectionStrings.config
configuration file, Creatio will replace the connection property of the dedicated query pool with "creatio_Limited."Example of the set up App property of the connection string<add name="db" connectionString="App=creatio; Data Source=dbserver\mssql2016; Initial Catalog=BpmonlineSolution; Persist Security Info=True; MultipleActiveResultSets=True; Integrated Security=SSPI; Pooling = true; Max Pool Size = 100; Async = true; Connection Timeout=500" />
As a result, Creatio adds more database connections when you load dashboards or filter sections using dynamic folders. Unlike main connections, the names of these connections contain the _Limited
suffix.
Creatio does not limit resources when using a dedicated query pool. Use the _Limited
suffix to rate connections with Resource Governor tools.
2. Enable the dedicated query pool functionality
To enable the dedicated query pool functionality, set the UseQueryKinds
key of the <appSettings>
element in the ..\Terrasoft.WebApp\Web.config
file to true
. The UseQueryKinds
key lets you send queries from dashboards and dynamic folders to connections whose names contain the _Limited
suffix.
<add key="UseQueryKinds" value="true" />
3. Set up the Resource Governor tool
The setup of the Resource Governor tool involves the setup of the groups and pool.
To set up the Resource Governor tool, execute the SQL script.
ALTER RESOURCE POOL poolLimited WITH (
MAX_CPU_PERCENT = 20,
MIN_CPU_PERCENT = 0
-- REQUEST_MAX_MEMORY_GRANT_PERCENT = value
-- REQUEST_MAX_CPU_TIME_SEC = value
-- REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value
-- MAX_DOP = value
-- GROUP_MAX_REQUESTS = value
);
GO
--- Create a workload group for off-hours processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP groupLimited WITH (IMPORTANCE = LOW) USING poolLimited
GO ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Learn more about setting up the tool: official vendor documentation (Resource Governor).
The classifier function that returns the group name is run for each new connection.
USE [master]
GO
ALTER FUNCTION [dbo].[fnProtoClassifier]()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
IF(app_name() like '%_Limited')
BEGIN
RETURN N'groupLimited'
END
RETURN N'default'
END;
Read-only replica
Creatio supports reading data from a read-only replica. The purpose of a read-only replica is to process complex Select
queries.
Creatio can redirect the following queries:
- Custom
SelectQuery
from Creatio UI. Select
queries from the back-end. For example, the Script task process element.
Similar to a dedicated query pool, a read-only replica only accepts Select
queries that are not part of the transaction. Creatio supports only a single read-only replica.
Learn more about setting up a read-only replica: Process complex database queries faster (user documentation).
Execute a complex Select query
To execute a Select
query, retrieve a special DBExecutor
by passing the Limited
value from the QueryKind
enumeration as an additional parameter.
QueryKind
is the argument of the EnsureDBConnection()
method in the example below. Creatio retrieves the argument value from the ESQ
client query and inserts the value into the ESQ
server query, as well as the Select
query.
using (DBExecutor executor = userConnection.EnsureDBConnection(QueryKind)) {
/* ... */
};
The EnsureDBConnection(QueryKind.General)
call is identical to the EnsureDBConnection()
call that does not specify QueryKind
.
As a result, if you select the QueryKind.Limited
flag in the front-end when you create an instance of the Terrasoft.EntitySchemaQuery
class, Creatio will pass the value to the server and assign the query a special DBExecutor
that uses a dedicated query pool.
...
getChartDataESQ: function() {
return this.Ext.create("Terrasoft.EntitySchemaQuery", {
rootSchema: this.entitySchema,
queryKind: Terrasoft.QueryKind.LIMITED
});
},
...
If the code contains nested calls to userConnection.EnsureDBConnection(QueryKind)
, make sure to use the same QueryKind
value on every nesting level.
See also
Process complex database queries faster (user documentation)
Resources
Resource Governor (official Microsoft documentation)
SQL Server Replication (official Microsoft documentation)
Simple Streams Replication Configuration (official Oracle documentation)
Streaming Replication (official Postgres documentation)