Skip to main content
Version: 8.1

Process complex database queries faster

Some Creatio database queries take a long time to process, which might affect page loading or task completion time significantly. Such queries are usually called “heavy.” They include:

  • complex filters in pages and dynamic folders
  • complex analytical selections in section dashboards
  • complex custom queries implemented using development tools

You can accelerate the processing of heavy queries by forwarding them to a read-only database replica. This will reduce the load on the main database significantly and free up resources for the activity of users and the operation of other Creatio elements.

To set up the redirection of heavy queries, take the following steps:

  1. Create a read-only database replica.
  2. Configure access to the database replica in Creatio.

Step 1. Create a database replica

The procedure to create a database replica is DBMS-specific. Learn more about the process in vendor documentation:

Step 2. Set up redirection of heavy queries

  1. Set up redirection of heavy queries to the database replica. Perform the setup in the Terrasoft.WebHost.dll.config file for Creatio .NET Core and .NET 6 and in the web.config file for Creatio NET Framework.

    1. Select the UseQueryKinds checkbox.

      <add key="UseQueryKinds" value="true" />
    2. Add the replicaConnectionStringName="db_Replica" value to the db general parameter.

      <general connectionStringName="db" replicaConnectionStringName="db_Replica" securityEngineType="Terrasoft.DB.MSSql.MSSqlSecurityEngine, Terrasoft.DB.MSSql" executorType="Terrasoft.DB.MSSql.MSSqlExecutor, Terrasoft.DB.MSSql" engineType="Terrasoft.DB.MSSql.MSSqlEngine, Terrasoft.DB.MSSql" metaEngineType="Terrasoft.DB.MSSql.MSSqlMetaEngine, Terrasoft.DB.MSSql" metaScriptType="Terrasoft.DB.MSSql.MSSqlMetaScript, Terrasoft.DB.MSSql" typeConverterType="Terrasoft.DB.MSSql.MSSqlTypeConverter, Terrasoft.DB.MSSql" enableRetryDBOperations="false" retryDBOperationFactoryType="Terrasoft.DB.MSSql.MSSqlRetryOperationFactory, Terrasoft.DB.MSSql" binaryPackageSize="1048576" currentSchemaName="dbo" enableSqlLog="false" sqlLogQueryTimeElapsedThreshold="5000" sqlLogRowsThreshold="100" useOrderNullsPosition="false" maxEntitySchemaNameLength="128" />
  2. Configure access to the database replica in Creatio. To do this, add the db_Replica parameter to the ConnectionStrings.config file:

    <add name="db_Replica" connectionString="Data Source=[ Database server name ]; Initial Catalog=[ Database name ]; Persist Security Info=True; MultipleActiveResultSets=True; Integrated Security=SSPI; Pooling = true; Max Pool Size = 100; Async = true" />

See also

Requirements calculator

General Creatio deployment procedure

Set up a dedicated query pool (developer documentation)