You can translate the question and the replies:

Denodo query simplification taking a long time.

We have a complex final view that utilizes many (20+) base views. All of the base views are cached so the query execution plan in very simple. However, in looking at the execution plan we can see that the optimizer is taking 8-9 seconds in query simplification before it pushes down everything to the cache database (SQL Server 2017). The query simplification does not appear to gain us anything in this particular case because once the query is pushed down, it executes in 3-4 seconds regardless of whether it has been simplified or not (we experimented by turning off database simplification). In fact, its overhead is adding to slow query response time. So my question is, is there any way to turn off query simplification on an individual query against a derived view that utilizes these base views? Alternatively, are there any things we should look for to help explain why the query simplification is taking so long? We already have statistics and indexes on all the base views. We are on 7.0.20181011. thank you!
VDP query optimisation
user
28-06-2019 11:07:29 -0400
code

1 Answer

Hi, If you want to turn off the Automatic Query Simplification for a particular derived view you can add a CONTEXT clause. For example, you can query your derived view as SELECT * FROM derived_view CONTEXT (‘simplify’ = ‘off') Besides that you can also use the Resource manager to disable the automatic simplification of queries based on a specific role, Virtual database, user name, etc. refer the section Resource Manager from the below link for the steps to follow in creating the plans and rules. https://community.denodo.com/docs/html/browse/6.0/vdp/administration/resource_manager/resource_manager#resource-manager Ideally, the automatic query simplification loads the results in less time, even for complex joins but if you are a customer and want to more assitance in reviewing the view, you can open a support case. Hope this helps!
Denodo Team
01-07-2019 21:15:17 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here