How to optimize the User Account Mappings in Database Account Data Collector

[JaydeepMehta]

Hi, 

We are collecting the Accounts and User Account Mapping from ADC of type  Oracle Database. In our database, we have Accounts and User Account Mappings in same table. How to optimize same, as per collector accounts will be collected running  query on account table and then again it will query database for User Mappings on the same table. How to optimize collection in that case. We have larger dataset to be collected and this is adding up cost to run collection.

Demo Data

Accounts Table

Query for Accounts and User Account Mappings 

Select ID, UserID from AccountsTable

MyAccess Fields :: Database fields 

Account ID/Name :: ID Column in Database

User ID :: UserID in Database

Please advise asap.

Thanks,

Jaydeep

[MHelmy · Accepted solution]

The only thing you need to worry about from a product point of view is to not collect any duplicate accounts or relations. In most cases where both accounts and account-mappings are in the same table, the query for accounts only would contain a distinct (in case the same account is mapped to more than one user).

As for optimization of the query, that would fall outside the scope of our product. You would probably need to contact the DBA of the database you are collecting from to help with that bit.