Query Optimization and Caching
What this domain tests
Optimization questions test whether you can explain how VQL becomes source queries, when the optimizer delegates work, and when cache or MPP changes the picture. You should distinguish first-execution cost from repeated-query cost and know that caching is a governance decision as much as a performance one.
Core rules
- The cost-based optimizer rewrites queries, chooses join order and algorithms, and decides what to push to each source. Delegation depends on source capabilities and the functions used.
- Push filters and aggregations early to reduce rows before joins. A join that cannot be pushed is evaluated in VDP or, when enabled, in embedded MPP.
- Cache can be memory or database-backed, full or partial, with invalidation by time-to-live, schedule, manual refresh, or source-dependent rules. Cache preloading warms results before consumers arrive.
- Summary-based acceleration can rewrite queries to smaller precomputed summaries; it is not the same as caching a full result.
- Use EXPLAIN plans and the query monitor to see the delegated SQL, rows processed, and time breakdown. Stale statistics lead to poor join choices.
Worked example
A dashboard joins Oracle orders to Salesforce accounts and filters the last 30 days. The optimizer pushes the date predicate to Oracle, attempts to push the account filter to Salesforce, and joins the reduced sets in VDP. Because the dashboard refreshes every five minutes, create a 10-minute cache on the interface view and preload it with the Scheduler. The reasoning: reduce data at the source first, avoid repeated federation, and accept bounded staleness explicitly. If the query were ad hoc and highly selective, caching every result would be wasteful.
Traps that cost marks
- Saying cache speeds up the first query; it does not.
- Ignoring stale cache after source updates.
- Assuming every function or join is delegated.
- Caching regulated data across regions without checking sovereignty rules.
- Treating MPP as a substitute for good filters and source pushdown.