, , ,

Optimizing Business Central Indexes for Performance with Copilot

Maintaining Business Central database indexes is essential for optimal performance. Utilizing Excel, users can analyze missing index data to generate AL code recommendations. Factors include prioritizing indexes by estimated benefit, consolidating similar indexes, and managing include columns. Proper implementation and performance measurement are crucial to ensure effective indexing without negative impacts on data writes.

Marcel Chabot
2–3 minutes
, , , , , , ,

Maintaining Business Central database indexes is critical for top system performance. We discussed them way back in BC AL Journey #16.

A Guide to Keys and Indexes in Business Central

Index management can be a challenge if you are new to the process. It can be tedious if you have been doing it a lot. This is where Copilot can really help you. Export your Database Missing Indexes table to Excel. Once in Excel run this prompt:

Review the current Excel sheet which displays SQL Server database missing index data from a Business Central system. The data includes columns for Table Name, Extension Id, Index Equality Columns, Index Inequality Columns, Index Include Columns, Seeks, Scans, Average Total Cost, Average Impact, and Estimated Benefit.

Please analyze this data and generate AL code index recommendations with the following requirements:

1. **Prioritize by Estimated Benefit** - Focus on the highest-impact indexes first

2. **Generate proper AL tableextension syntax** including:
   - Appropriate key names
   - Key fields in correct order (equality columns first, then inequality columns)
   - IncludedFields property where the data suggests include columns would help

3. **Consolidate similar indexes** - If multiple rows suggest similar key patterns, combine them into a single optimized index that covers multiple query patterns

4. **For Include Columns**:
   - If SQL Server suggests a small number of include columns (under 15), include them all
   - If SQL Server suggests many columns (30+), trim to the most commonly needed fields for that table type (amounts, keys, dimensions, dates, status fields)
   - Explain the trade-off between index size and query performance

5. **Group results by table** - Create separate tableextension objects for each table

6. **Provide implementation notes** including:
   - Priority order for implementation
   - Any concerns about write performance impact
   - Alternative approaches if indexes alone won't solve the issue

Generate complete, copy-paste ready AL code.

You can add additional context to the prompt such as the “Chart of Accounts is slow” or “Sales invoice posting process is slow”. The additional context will help the Copilot system prioritize some indexes over others.

The result will be some AL Extension code ready for review and implementation.

We aren’t going to blindly implement AI Generated Code just because some blogger told you to, right? Right? Here is what we are going to do.

  1. Benchmark system performance.
    • Read and Write to the Tables you are about to index.
    • Be aware of processes that utilize those tables, measure those too.
    • Use the Performance Profiler to get Real Numbers.
  2. Apply the Indexes
    • Don’t add more than 5 or so at a time.
    • After application wait a few hours.
  3. Benchmark system performance.
    • Same as step 1.

From here you can decide if your indexes were successful or not. Remember Indexing improves data retrieval at the cost of data writes. This is of primary concern with indexing the GL Entry table. Over indexing the GL Entry table can make reporting much faster, but processes like payroll may take longer. Just be sure to measure all your processes to make an educated decision.

Leave a comment

Trending