Forum Discussion
Why does ADX caching result from related dimension table/mv/function
Bartek_insight ADX caches the results for a certain period by default to avoid unnecessary re-computation. This caching behavior can lead to data needing to be updated promptly. Unfortunately, there is no direct way to force an immediate refresh of the cached results. However, you can try the following approaches:
1- Adjust Cache Policy: Check if you can adjust the cache policy for the materialized view or function. You can reduce the cache duration or disable caching altogether, but this could impact query performance.
2- Use a Hybrid Approach: Consider using a hybrid approach in which you periodically refresh the materialized view or function. For example, schedule a job to refresh the data regularly, like every hour. This way, you balance performance gains from caching with the need for fresh data.
Lastly, if real-time data is critical, you might need to manually trigger a refresh. You can do this by re-creating the materialized view or invoking the function again. This approach requires careful handling to avoid unnecessary overhead.
Remember that caching is a trade-off between performance and freshness. Evaluate your specific use case and choose a strategy that best aligns with your requirements.
I suggest you read:
Guy_Reginiano post on Monitor and optimize the cache usage with ADX Insights
and
Bhaskar post on ADX Query Performance Unleashed: Best Practices and Pro Tips
--
If this post is helpful, please give my response a thumbs up! You can also mark it as the solution to help others find it easily.
Thanks
BabatundeDallas thanks for your input. I will comment your proposals below:
1. Caching policy has nothing to do in this case, it is only about storage setup if we want to store data in RAM/SSD or regular disks.2. What do you mean by refreshing materialized view?
3. Yes, real-time is a must, so I would need to recreate the materialized view each few seconds with backfill of 14 days, so it won't suite here.
It, would be great if materialized view give an option to switch off subquery/lookup results...
Bartek_insight You’re correct that the caching policy doesn’t directly impact your scenario; it was just a possible suggestion for debugging.
When I mention “refreshing,” I mean updating the materialized view with fresh data. In ADX, materialized views are precomputed and stored. To refresh them, you’d typically recompute the view based on the underlying data. However, your requirement for real-time data makes frequent recomputation impractical.
Recreating the materialized view every few seconds with a 14-day backfill is inefficient because it’s resource-intensive and may not align with your use case. I understand your need for an option to control subquery/lookup results. Unfortunately, ADX doesn’t currently provide a direct switch to disable caching or force immediate refresh for subqueries.
However, I recommend exploring alternative approaches like Custom Logic, Scheduled Recomputation, or a Hybrid Approach.
1) Implement custom logic in your queries to handle real-time conditions. For example, materialized view results can be combined with fresh data using UNION or other techniques.
2) Schedule periodic recomputation of the materialized view, such as an hourly one, if feasible. This balances performance and freshness.
3) Or keep the materialized view and query the raw data directly when real-time accuracy is essential. Use the materialized view for historical data and the raw data for recent updates.Note: ADX doesn’t offer an immediate solution to disable subquery caching. Exploring these alternatives might help you achieve your real-time requirements. I will contact the Microsoft developer team about new features that would provide ADX with a direct switch to disable caching or force immediate refresh for subqueries.
--
If this post is helpful, please give my response a thumbs up! You can also mark it as the solution to help others find it easily.
Thanks
BabatundeDallas thanks for your reply,
please explain what do you mean by recomputation of materialized view?Drop and recreate, or what kind of command?
