Database Aggregation Taking time

[ankeetarjyal (Ankit Aryal)]

We are seeing an issue on a database application aggregation. We have enabled partition for the application and have around 102000 accounts in the application. Once the aggregation kicks off, the other applications aggregation and other tasks hang on pending state for a long time. The application in question has taken days to aggregate and is making sailpoint slower and also throws error when searching for any identity during the aggregation is in progress. Has anyone seen similar issues on DB aggregations?

[vishal_kejriwal1 (Vishal Kejriwal)]

Are you using “Data needs to be merged” ?

[abhishek_chowdhury]

Can you share results of database performance test of your enviornment.

[enistri_devo (Emanuele Nistri)]

Hi @ankeetarjyal ,

the problem could depedes from various causes. When SP aggregates data from a DB, thare 4 sptes:

1. Send the query to target DB
2. target DB elaborates the query and send the result to SP
3. The task server elaborates the data appling the rule
4. SP DB save the data

So, first of all you need understand how much time target DB need for elaborate and send the result. it’s almost impossible, but not entirely, that a DB need days for a query.
Later you need to check how much rule are launched for the aggregation, how much record and columns are elaborates and the performance of SP DB.

It could be, you have a problem some locks on DB, or a low resource for task and DB server, or a firewall between task and DB server(SP doesnt want).

In every case, these are the improvements you can make:

• Analyze and optimize the query
• Delete the colums from query that are not necessary, if its possible
• Analyze and optimize the rules of connector, if you have
• like @vishal_kejriwal1, active Data needs to be merged if an account have more record
• Improve the number of partitions and threads for aggrergaton and task
• Analyze the performace of SP DB, like @abhishek_chowdhury says

For the first point, if the target DB need a lot of time for execute the query you can ask to DBA to create a Stored procedure further optimize the query.
For the last 2 point, you can increase the number of task server and increase the resource for SP DB.

[paul_hilchey]

When you check the task result, have the partitions been created? Are they progressing or are they stuck?

[vinnysail (vinod kumar maram)]

Hi @ankeetarjyal
You can start looking into the partitioning best practices article - https://community.sailpoint.com/t5/Other-Documents/Partitioning-Best-Practices/ta-p/74964
you can navigate to debug page and traverse to “Request definition” → “Aggregate Partition” see if you can increase the maxThreads count based on the CPU’s and task servers that you have. Please refer the best practices article to see more information.