The “SelectLatestVersion” Trap: Surgical Cache Refreshing
Have you ever investigated a “ghost data” bug where heavy integration was reading old values from BC cache. Most of us developers opt to clear the cache by sprinkling Database.SelectLatestVersion() across the code like confetti. Sure, data will be fresh, but performance-wise, it will be tanked. SelectLatestVersion is a nuclear option.
⚠️ The Problem: Global Cache wipe
In standard AL, calling SelectLatestVersion() is a global command. It tells the service tier to ignore every cached value for every table in that session.
If you just need a fresh look at a specific Integration Log table, you’re forcing the system to re-fetch the Sales Header, User Setup, and GL Setup from SQL too. You’ve successfully traded a data bug for a massive SQL round-trip bottleneck.
🛠️ The Fix: Table Specific Cache Refresh
Introduced recently, we finally have the ability to target the cache refresh. By passing the Table ID to the method, you tell the platform: “Keep everything else in the cache, but give me the absolute truth from SQL for this specific table.”
❇️ Technical Grounding:
✅ SaaS Available : Yes
✅ Runtime/Upgrade : Runtime
✅ Bypasses Triggers : No, It affects the data retrieval layer.
💡Why It Matters
➡️ Drastic SQL Reduction: You maintain the benefits of the BC cache for 99% of your session data, only hitting the database for the “volatile” 1%.
➡️ Scalability: In high concurrency environments (like web services), this prevents the “Cache Miss Storm” that slows down the entire service tier.
➡️ Consistency: Perfect for scenarios involving external middleware where data changes rapidly outside the current AL session.
☣️ The Trap: Don’t use this as a crutch for poor transaction handling. If you find yourself needing this on every other line, your Commit() logic or transaction scope is likely the real culprit.
✴️ The BottomLine
Clearing the cache and reading data directly from the database adversely affects performance.
Aim small miss small – keep everything scoped, precise, and efficient.