I would check the query’s execution trace to find each data source's execution time and isolate the datasource which is requiring a lot of processing. This would help me to determine which parts of the query might require further optimization. Also I would enable the [Denodo Monitor Tool](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Monitor%20Reports%20-%20User%20Manual) to monitor the Server performance.
When I perform the join that result in a large number of rows, I make use of the different query optimization methods that are available, such as:
1. [Optimizing Join Operations](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations#optimizing-join-operations).
1. [Cost-based optimization](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/cost-based_optimization/cost-based_optimization).
1. [Data movement](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/data_movement/data_movement).
1. [Automatic simplification of queries](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/automatic_simplification_of_queries/automatic_simplification_of_queries).
1. [Parallel Processing](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/parallel_processing/parallel_processing).
I would recommend you to take a look at the Knowledge Base article Best Practices to maximize performance - “[Configuring the Query Optimizer](https://community.denodo.com/kb/view/document/Best%20Practices%20to%20Maximize%20Performance%20II:%20Configuring%20the%20Query%20Optimizer?category=Best+Practices)” and “[Detecting Bottlenecks in the Query](https://community.denodo.com/kb/view/document/Best%20Practices%20to%20Maximize%20Performance%20IV:%20Detecting%20Bottlenecks%20in%20a%20Query?category=Best+Practices)” which discusses query optimization and detecting bottleneck in very detail.
Hope this helps!