Resolving MongoDB Connector Performance Issues in Sisense
Summary
This article addresses performance issues caused by the MongoDB connector in Sisense, specifically related to the addition of an unnecessary parameter in queries and as a result, leads to inefficient query execution and overall system performance degradation. The article provides steps to resolve the issue and improve performance.
Main Content
Issue Description
The MongoDB connector in Sisense may add an unnecessary parameter {"$not": {"$type": 2}} to queries translated from SQL to MQL. This additional parameter prevents the use of indexes in the execution plan, resulting in full table scans and significantly increased query times. This issue affects the initialization step of the EC build, causing delays in Cube builds and overall system performance failures.
Steps to Resolve the Issue
- Identify the MongoDB Connector Version:
- Ensure you are using the MongoDB connector version 1.1, which is auto-installed as part of Sisense Fusion.
- Disable Mixed Type Filter:
- Use the parameter EnableMixedTypeFilter=0 to remove the additional filter {"$type": 2} from the queries.
- This can be done by adding the parameter to your MongoDB connector configuration.
- Verify Query Performance:
- After applying the parameter, monitor the query performance to ensure that the unnecessary filter is removed and indexes are being used.
- Use MongoDB Atlas or other monitoring tools to check the "Examined:Returned Ratio" for your queries.
- Upgrade MongoDB Connector:
- If the issue persists, consider upgrading the MongoDB connector to a version that supports MongoDB Version 6.0.16.
- Contact Sisense support to get assistance with the upgrade process.
Troubleshooting Tips
- Check Indexes:
- Ensure that all required indexes are added to your MongoDB collections.
- Use MongoDB Atlas or other tools to review and optimize your indexes.
- Monitor Query Insights:
- Use MongoDB Atlas - Data Services - View Monitoring - queryInsights - profiler to monitor the performance of your queries.
- Identify queries with a high "Examined:Returned Ratio" and investigate further.
- Review Execution Plans:
- Analyze the execution plans of your queries to ensure that indexes are being used effectively.
- Look for any additional filters or parameters that may be affecting performance.
