As your database grows with your business’s ever increasing transaction history, slow page loading and searching can become a common user complaint in Microsoft Dynamics 365 Business Central. Don’t dismay – this problem isn’t as daunting as it might seem! As a consultant who is often responsible for improving system and database performance, I thought it would be useful to share my insights of common recommendations I share with customers and colleagues.

Improve search efficiency with better filtering

The worst offender for list page speed is the [🔎 Search] box, because this searches across all visible fields on the list page, in “wildcard” style – meaning that the search string can match anywhere in the column (at the beginning, middle or end). For large historical tables this is a very expensive task for SQL to fulfil. This means that page results can be slow, and timeouts can arise.

Training users how to filter efficiently will help them find their desired data much faster:

  • Learn to use the Filter Pane, which returns results faster than the Search box because it does not wildcard. search and will only search a specific column.
  • Learn the hotkeys and shortcuts for filtering.
    • Shift+F3 to toggle the filter pane.
    • Shift+Alt+F3 to toggle the filter pane, ready to enter a filter on the currently focused column.
    • Alt+F3 to set a filter on the currently focused column, to the focused cell value.
    • Clicking on the chevron next to each column header opens a menu of different filtering options.
  • The ability to Save Views is an under-used feature that helps users to quickly apply their common filters.
How to filter and save a filter in Business Central
How to add a filter and save a filter in Business Central

Microsoft has an overhaul of the wildcard Search box available in Preview (see later section of this article), which will improve speed greatly. However, learning how to filter efficiently will always be a useful skill to improve Business Central page performance.

Managing data growth for better performance

The smaller the table, the faster it is to search and filter. As databases age and accumulate many years of transactions, it is common to find that performance complaints grow. Deleting or archiving unnecessary data is an important check on the slow-down of the system over time, and it will save you from needing to expand your on-premises or Cloud storage.

  • Use the Table Information area to see the largest tables.
  • Delete old and unneeded data such as unposted Sales Orders and Purchase Orders, unposted stocktake journals or other journal batches. This will also remove unnecessary reservations (if you are reserving inventory).
  • Use Retention Policies to schedule the automatic deletion of data past a certain age.
  • Compress old ledger data by summarising it. Engage your Business Central partner for assistance.

Addressing performance pitfalls in growing databases

A design that works fine at a low volume of data can break down with 100 or 1000 times the data size, and imperfections that never posed a problem can become a major issue. This section covers some common pitfalls that can compromise page speed and are worth tackling in a large and growing database.

It is a great idea to use Business Central’s profiling and observability tools to direct efforts to the highest value optimisations. You can also use these tools to generate a baseline against which you can compare improvements.

Optimising page design for faster searchers

  • Remove unimportant fields. Because the wildcard Search box checks all visible fields, removing fields improves search speed.
  • Remove unnecessary FlowFields. For example, are both the “Amount Including GST” and “Amount Excl. GST” fields necessary?
  • If a FlowField is useful but not needed all the time, then you can move the FlowFields from a column on the list into a Factbox instead. The system will only calculate the FlowFields when the row is highlighted and this improves responsiveness.
  • For very large historical tables, you can consider creating an ‘advanced’ version of a page, where the full extended set of fields are present. Then you can alter the regular version of the page to cut down the fields to the very bare minimum needed for everyday use.

Improving sort orders and filters for large tables

  • Make sure the default sort order (SourceTableView) and any commonly applied sort orders and column filtering patterns have matching keys (database indexes).
  • For very large historical tables:
    • Set the default sort order to primary key order. For example, is necessary to sort Sales Shipments by the Shipment Date? Sorting by Shipment No. will be fine.
    • Consider applying default filters to hide old data. Users can clear the filter if they need to find very old data. The filter will perform best if set on the primary key field and the page sort order is by primary key as well.

Enhancing performance by refining code triggers

  • Remove or re-work code on the OnAfterGetRecord trigger, if there is any
    • If a calculated field is needed, then a FlowField will perform better than code performing the same logic through manual calculation in this trigger (because a FlowField is executed in batch, whereas OnAfterGetRecord is run row-by-row).
    • Be aware of the difference between OnAfterGetCurrRecord and OnAfterGetRecord and use the right one.
    • If you need a manual calculation (e.g. because a FlowField will not support the use case), prefer implementing the calculation in a Factbox rather than in a list column. This way, Business Central will only calculate when the record is highlighted.
    • For card pages, the OnAfterGetRecord trigger is executed (and FlowFields and Factboxes are recalulated) after any page action is invoked, so inefficient code in this trigger will make a page feel sluggish.
  • Avoid using OnFindRecord and OnNextRecord triggers where possible. When this trigger is present, it prevents the partial records behaviour from working correctly, and hampers performance.

Recent Business Central features

There are two new features currently in Preview which should greatly enhance the data search experience for users. (Preview features are available to opt-in in Business Central Online and latest versions of on-premises Business Central, but not yet finalised).

Search in company data

The Search in Company Data feature allows a user to search many tables and columns at once. This could remove the need to navigate the list page at all, in many cases, by allowing the user to jump straight to the right record when they have the reference number (e.g. an Invoice No. or External Document No.).

Modern search is a major step up from the existing wildcard Search box:

  • Term/word order does not need to match exactly.
  • The results are returned faster, because database indexes are maintained on the fields which are searched.
  • Columns to be indexed are configurable by partners and extension developers, which enables the most relevant fields to be searched and others to be ignored.
New modern search bar in Business Central

Recent platform optimisations

Since Microsoft began self-hosting Business Central in their own Business Central Online platform, they have focused on improving the performance of the product and the community at large has benefited from this. If you are an on-premises customer with a large database, it is important to upgrade to the latest version of Business Central so that you receive important performance improvements. An upgrade project also gives you a good opportunity to clean up historical data and make a fresh start.

Some notable performance improvements to the platform from recent releases:

Business Central 2021 wave 2

Business Central 2022 wave 2

Business Central 2023 wave 1

Business Central 2023 wave 2

In addition to the above, there has also been many usability improvements in the browser client, and improved tools for partners to analyze performance issues in the system.

The value of regular system performance reviews

A fast and responsive system keeps users happy and increases productivity. Periodic system performance reviews are important to ensure that small problems do not become larger problems as the business and the database grows. Regular optimisation efforts deliver long-term dividends by maintaining high user confidence and preventing emergencies when the system reaches its limits.

Fenwick has extensive experience in tuning the performance of Business Central and SQL Server, so if you would like assistance or support with your Business Central environment, we are here to help.