Optimizing Business Central Indexes for Performance with Copilot
The post shows how to export the Database Missing Indexes table to Excel and use a Copilot prompt to generate AL tableextension index recommendations. It then advises benchmarking with the Performance Profiler before and after, applying only about five indexes at a time, and weighing faster reads against slower writes, especially on GL Entry.
post Aardvark Labs
"Remember Indexing improves data retrieval at the cost of data writes. This is of primary concern with indexing the GL Entry table."
Key points
- Export the Database Missing Indexes table and use Copilot to generate AL code recommendations prioritized by estimated benefit
- Benchmark system performance before and after applying indexes, measuring both read and write operations
- Implement indexes incrementally (5 or fewer at a time) and wait several hours between applications to evaluate impact
- Balance query performance gains against write performance costs, especially for high-volume tables like GL Entry
Quotes
- “Remember Indexing improves data retrieval at the cost of data writes. This is of primary concern with indexing the GL Entry table.” (Highlights the critical trade-off to consider when optimizing indexes, especially for financial tables)
- “We aren’t going to blindly implement AI Generated Code just because some blogger told you to, right?” (Emphasizes the importance of testing and validation before applying any generated code to production)
Context
- Features: Database Missing Indexes table export, Copilot prompt for AL index generation, Performance Profiler, Table extension syntax for indexes
Source: Aardvark Labs, community blog. Summary, key points and quotes are derived (CONTENT-NOTICE.md); read the original for the full text.