Setting the MAX_IN_CLAUSE, MAX_OR_CLAUSE and MAX_JOINS parameters

First published: 28 April 2023 (cf)

Summary

The Fabasoft COO-Services uses IN clauses, OR clauses and JOINs in their SQL queries to request data from the database. This is used to collect the data of objects from the different data tables, and to apply filters for database queries.

These parameters set a limit of maximum IN, OR and JOIN clauses. In some situations, the database system is not capable of using that many IN, OR or JOIN combinations, so it is possible to adjust Fabasoft default values.

You only should adjust the parameters if your database produces errors because of that queries, or in coordination with Fabasoft.

Information

The parameters are set as database parameters in the Fabasoft Folio COO Service object.

The parameters and default values are:

These parameters are used as default, if no parameter is set in the COO Service object.

Solution

If you want to set one of the parameters, navigate to the Domain Administration / Services tab.

Edit the COO Service object

Add the required parameter to the “Additional Database Parameters” property.

Save the object.

Repeat this for every COO Service you want to adjust.

After changing the parameters, open the Fabasoft Server Management, navigate to the domain and run “Synchronize Registry”.

This will restart your Fabasoft Folio Backendservices. This should be done in a planned downtime.

Keep in mind, that changing one value has direct impact to the other type of query. For example, reducing the IN clauses will cause the COO Service to create queries with more OR clauses, and vice versa. Therefore, reducing all parameters may lead to query errors, as the queries cannot fulfill the requirements.