Caching, Query Acceleration, and Performance Tuning
What this domain tests
This domain covers how VDP avoids repeated work: cache modes and invalidation, precomputed summaries, MPP acceleration, and the tuning levers visible in the execution plan.
Cache modes
Cache is configured per view with modes off, partial and full. Partial cache stores results as they are retrieved and can answer later queries, including subquery fragments, without preloading. Full cache serves only from cache and therefore requires preloading or refresh, typically scheduled. Storage may be memory or disk depending on size and persistence needs. Invalidation is by time-to-live, explicit refresh, or scheduled job.
Acceleration
Summaries are pre-aggregated, pre-joined datasets stored in VDP's own storage or an MPP engine; the optimizer rewrites a matching query to read the summary instead of the sources. Denodo's intelligent query acceleration recommends summaries from observed workloads. The embedded MPP handles star-schema style aggregation at scale. Summaries only help when the query shape matches what was precomputed, including filters on the aggregation grain.
Tuning levers
Gather statistics so the cost-based optimizer can choose join order and strategy; inspect the execution plan to see pushdown and join methods; tune fetch size and parallelism; and use bulk load rather than row-by-row movement for large loads.
Worked example
A dashboard runs the same regional revenue aggregation every five minutes over a large fact table. Reasoning: a summary at region-by-day grain plus a scheduled refresh satisfies the query shape, and the optimizer rewrites the dashboard query to the summary. Caching the exact query in full mode would also work but is brittle when the date filter shifts, and memory cache would evict under other traffic. If users need current-minute data, neither is appropriate - tune the source query instead.
Traps that cost marks
- Full cache with no refresh schedule, serving indefinitely stale results.
- Expecting security filters to be re-evaluated against cached data.
- Building summaries at a grain the queries never filter on.
- Tuning heap when the real problem is a missing statistics gather.