Slow performance on Secondary Database connection in Portrait Dialogue explained

Product Feature: Databases
There are many factors that influence the performance of the three tier application server- web server - database server architecture. Following are the examples:
  1. Network Devices
  2. Web Server
  3. Application Server
  4. Database Server
A Secondary Database connection fetches data from different database. This puts a massive load on the system in run time.

To execute a query that does a cross database (or cross schema in the case of Oracle) join, Dialogue executes individual queries for every single participant it selects
in a dialogue. So, the initial selection is one query against the customer domain, and then one by one, it will do INSERT INTO dlg_participant VALUES(x, y, z) for each customer/participant that needs to be inserted into the dialogue. It gets even worse in case of one-to-many subgroups on the domain and have filters in the selection on those subgroups.

Comparative test has been done on SQL server with effectively the same domain configuration, one test with secondary database connection and the other without (i.e. cross database join query). A simple selection against a customer database with 300,000 customers, and where the selection will select 136,000 customers into the dialogue.
  1. With secondary database connection for the domain: 6 minutes 9 seconds (369 seconds). 368 per second.
  2. With default connection for domain, cross database join, default chunking + threading: 35 seconds. 3885 per second.
  3. With default connection for domain, cross database join, chunking + threading disabled: 11 seconds. 12363 per second.
Slow execution may be observed due the Secondary Database connection setup.
UPDATED:  November 22, 2017