Skip to main content

Determining the required number of database connections

As a rule of thumb, the higher the number of database connections opened for Compose for Data Warehouses, the more tables Compose for Data Warehouses will be able to load in parallel. It is therefore recommended to open as many database connections as possible for Compose for Data Warehouses. However, if the number of database connections that can be opened for Compose for Data Warehouses is limited, you can calculate the minimum number of required connections as described below.

  1. For each task, determine the number of connections it can use during runtime. This value is specified in the Advanced tab in the window. When determining the number of required connections, various factors need to be taken into account including the number of tables, the size of the tables, and the volume of data. It is therefore recommended to determine the required number of connections in a Test environment.
  2. Calculate the number of connections needed by all tasks that run in parallel. For example, if three data mart tasks run in parallel and each task requires 5 connections, then the number of required connections will be 15.

    Similarly, if a workflow contains two Storage Zone tasks that run in parallel and each task requires 5 connections, then the minimum number of required connections will be 10. However, if the same workflow also contains two data mart tasks (that run in parallel) and the sum of their connections is 20, then the minimum number of required connections will be 20.

  3. Factor in the connections required by the Compose for Data Warehouses Console. To do this, multiply the maximum number of concurrent Compose for Data Warehouses users by three and then add to the sum of Step 2 above. So, if the number of required connections is 20 and the number of concurrent Compose for Data Warehouses users is 4, then the total would be:

    20 + 12 = 32