You can translate the question and the replies:

Summary Table Acceleration is not being Utilized

We are facing a senario where Summary Table Acceleration is not being considered as best plan for execution. We have followed the exact steps available in the Denodo Guide where we are creating a summary table on agreegated transformation. First case, where we have a UNION with where one view contains current month data and other view contain past 5 months data (which include agreegated data through analytical functions) and when we do a selection on top of it for past months data then it considered PUSH DOWN as best optimization plan for execution rather using summary table to gather the required result. Summary Table and Historical tables, both are present on same database. Second case, where we have the same SET of views and it hit the summary table acceleration but as soon as we collect STATS on Summary table, it decide to use PUSH down as optimal plan. This is very weird behavior and would appreciate any help. One possible solution we identified is to directly use SUMMARY table in the UNION view to force the acceleration but we want to know if that is also a good approach.
29-11-2022 12:25:11 -0500

1 Answer

Hi, In general, you can store the frequently accessed query result as Summaries. Summaries have some advantages over the traditional cache: * Summaries are transparent to the user. Unlike with caching, you do not need to create a view to cache a data set. The query optimizer will automatically analyze if it can rewrite the incoming queries to take advantage of the data in the summary without the user being aware of its existence. * Summaries are not restricted to the data source configured for caching. This means that you can create as many summaries as you want for the same view/view in different data sources. Please be informed that usually the aggregation functions are considered costly. Because when you have huge volume of data and the query cannot be delegated, Denodo has to fetch all the records and apply group by in virtual layer. With summaries, this process is simplified. It will directly fetch the result from summary table and apply any additional operation in virtual layer. The important thing to note here is that Denodo will see if the end user query can be resolved with the data stored in summary and then it will use those data. If any other operations like filter or join required, it will be done after that. For your scenario, we also suggest you to refer to Execution Trace to know more detailed information. For further clarifications, please refer to the following documents, * [Summary Rewrite Optimization]( * [Best Practices to Maximize Performance II: Configuring the Query Optimizer]( Hope this helps!
Denodo Team
02-12-2022 07:36:37 -0500
You must sign in to add an answer. If you do not have an account, you can register here