You can translate the question and the replies:

Poor performance for jdbc and json array type join

Hi, I observered a poor performance for a join between a jdbc type view and json array. individually when queried they are running fast but when we do a join it's performance degrades. Can you suggest how to improve performance ? Also i see 347 subplans not shown in execution plan. I dont know what that means can you suggest what it is ? Regards Salman Khan
user
09-10-2020 02:30:29 -0400

1 Answer

Hi, As you are doing a join using JSON data source the join operation happens in the Denodo layer and does not get pushed down to the underlying data source like joins between JDBC data sources. In such scenarios, I would do the following * I would check the execution trace and see if the appropriate join method is used. If the dataset is large and Nested join method is used, I would change it to Hash or Nested Parallel to see if there is performance benefit. Check the [Optimizing join operations](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations) Virtual DataPort guide for more information on join operations. * I would also check if [query optimization](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/automatic_simplification_of_queries/automatic_simplification_of_queries) and [cost based optimization](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/cost-based_optimization/enabling_the_cost-based_optimization#enabling-the-cost-based-optimization) are enabled as this will help Virtual DataPort choose the right execution plan. * I would make use of [Data Movement](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/data_movement/data_movement) optimization to see if it can be useful in this scenario. Regarding, sub plans they are generated for the query depending upon the join execution method. If the data set is large multiple sub plans will be generated and you can see this behavior. I would suggest you to try out the above options to improve the performance and avoid multiple subplans. For more information, you can also check this [Best Practices to Maximize Performance](https://community.denodo.com/kb/view/document/Best%20Practices%20to%20Maximize%20Performance%20IV:%20Detecting%20Bottlenecks%20in%20a%20Query?category=Best+Practices) and also [Cache Module](https://community.denodo.com/docs/html/browse/latest/vdp/administration/cache_module/cache_module) as it can also help improve the performance. Hope this helps!
Denodo Team
09-10-2020 08:19:14 -0400
You must sign in to add an answer. If you do not have an account, you can register here