When a user reports that they are facing slowness in performance for a specific MCLB configuration, may be that the SQL query is very complex. For ex: The Asset Heath Index query was reported as being slow and query analysis identified that the filtering was problematic.
Solution: - To improve the performance, we can send the SQL query to the respective DBA and ask them to investigate or add indexes to the database.In this case DBA recommended to remove the filters. DBA should analyse the query plan to see how it can be improved by:
-removing table scans
-adding indexes
-removing unnecessary tables from the query (remove columns from configuration)
Alternatively, try not making the configuration with too many filters as the default configuration in MCLB, or add strategic filters to limit data set returned
NOTE: - Try making the configurations less complex with less filters/ make the default configuration as simple as possible
SEE ALSO
Identify bad queries through logging: https://communities.bentley.com/products/assetwise/asset_performance_management/w/wiki/35086/investigating-performance-of-site-views