Join transformation resulting in high resource consumption

Matthew Davis (Technical Product Manager) 2 years ago in UNIFYBroker Service updated by Adrian Corston 1 year ago 3

Priority: Critical
Impact: Use of transformation renders whole UNIFYConnect environment unusable

A join transformation on a single valued field is causing high CPU, Memory and Database resource consumption. 

Image 6330

Source adapter has 7000 entities. Connector being joined to has 7100 entities. 

May be of interest: The field being joined to in the connector  has some values as "empty" rather than null.

Under review

Root cause was that the joined context had a large number of empty values rather than null, and so empty values in the source context were joining to a large number of entities in the target.

Short term solution is that implementation should avoid joining on fields which may contain a large number of join matches, particularly empty fields.

Leaving this one open for product team to investigate improvements to the join transformation, including ensuring the join window is applied at the appropriate time in the execution to avoid large numbers of records being resolved unnecessarily.

Note to self: this problem was occurring in one of my configs which was doing an AD User lookup to obtain manager AD account details based on employeeID which was NULL for many thousand entities.  Solution was to create a second "AD Employee" connector with the LDAP filter set to only import entities where employeeID was non-blank.