You can translate the question and the replies:

Denodo Union / Sort Merge Performance Issue

I currently have two derived views, say D1 and D2, that are unioned together to create D3. I originally had an issue with both D1 and D2 which involved a sort merge. Both D1 and D2 are a join of two base views; each ran very well when I had a date filter for just one day. However, when I filtered by a range of days, they ran very slow as the execution strategy then became a sort merge. The solution was to turn swapping off for both of these derived views which did indeed fix the problem of the sort merge. D1 now runs in around 6 minutes and D2 runs in 30 seconds. However, I when I run the union of these two views, D3, the query takes over 30 minutes to complete. I looked at the execution trace and it looks like both D1 and D2 turned back to utilizing a sort merge which is causing the issue. I turned swapping off for D3 as well and nothing changed. What can I do to fix this and improve performance? Thanks!
user
06-02-2020 09:16:18 -0500

1 Answer

Hi, It could be that the date filter is not being pushed down to the source which, instead, results in all of the rows being pulled into the virtual layer where the filter is then applied. To get some insight into why D3 is taking so long to execute, I would analyze its [execution trace](https://community.denodo.com/docs/html/browse/latest/vdp/administration/appendix/execution_trace_information/execution_trace_information), which will provide details about view's query plan. From there, you can decide on what changes are needed to optimize your queries. For more information on this topic, I would refer to the below documentation, which discusses query optimization in great detail: * ["Automatic Simplification of Queries"](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/automatic_simplification_of_queries/automatic_simplification_of_queries#automatic-simplification-of-queries) * ["Best Practices to Maximize Performance I: Modeling Big Data and Analytic Use Cases"](https://community.denodo.com/kb/view/document/Best%20Practices%20to%20Maximize%20Performance%20I%3A%20Modeling%20Big%20Data%20and%20Analytic%20Use%20Cases?category=Best+Practices) * ["Best Practices to Maximize Performance II: Configuring the Query Optimizer"](https://community.denodo.com/kb/view/document/Best%20Practices%20to%20Maximize%20Performance%20II:%20Configuring%20the%20Query%20Optimizer?category=Best+Practices) If you are a valid Support user, then you can create a Support case on the [Denodo Support Site](https://support.denodo.com/), where the Support team will help you. Hope this helps!
Denodo Team
07-02-2020 18:46:56 -0500
You must sign in to add an answer. If you do not have an account, you can register here