SEARCH in lists : the silent killer.

I knew about this – official and documented – behavior since a long time. My mind goes back discussing around it with my friend Vittorio in 2016-ish.

“Search” feature is way too easy to use.

Just type in what you want to search for and the application will find out what you are looking for. But what is the price to pay?

Everything is documented and under the sun:

About Searching and Filtering in Business Central – Business Central | Microsoft Learn

“Business Central applies the search criteria to all fields that are visible on the page. If a field has been hidden, such as by using personalization, search won’t consider this field. Search criteria are applied to fields only if their data type matches that of the search criteria. For example, searching for the term today will search all text and code fields for the literal value “today”, and any date fields where today is evaluated as an expression for the current date, but won’t search in any numeric fields.”

Translated for performance specialists.

There is a silent performance killer in every Search depending on:

1. The number of records.

For baby tables (baby = a small number of records) there is typically no problem. But the more records you have, the higher is the searching time.

• The number of columns to scan.

Every single column scan, even if you have everything perfectly in place with indexes etc., will add an extra delay on the research (typically depending on the number of records).

Let’s make a real-life example: I am looking for a price for a specific item.

• Item List (with few page extensions)
• 353.690 Items (records in Item table)
• Search for a string value like “11223344×10”

This will generate queries with a WHERE clause like

The necklace of OR – COLLATE – LIKE is the machine gun of the silent killer.

Each ring of this necklace, in this scenario, costs between 400 to 800 milliseconds and results in a total search time of 5 seconds approx.

Now, eyes on me.

You might say that you could leave with 5 seconds search BUT (there is always a but) if you try to click ANY action, let’s say for example Price List then the client will re-apply AGAIN the same query before opening the price list. And these are yet again other 5 seconds. Is it really necessary? It is not me to decide. It is what it is.

Do the math and be practical. To discover the price of an item in this scenario, you spend 10 seconds. If you have ever worked as accountant in a point of sale or have a potential customer in front of you waiting, 10 seconds are an eternity.

SOLUTIONS

• USE FILTERS = DO NOT USE SEARCH

That’s easy. What is the solution to this feature? Do not use this feature.

Using filters will only focus on the right column that you need to search for. Considering the previous scenario, filtering the item No. For that value will take less than 1.5 seconds, in worst case (cold cache).

The drawback is that users need to do few more clicks and get trained and used to filtering syntaxes. While training and get use could be viable, the few more clicks do not justify the gain you have in the change of habit: if a user try the search feature, he/she will always go for it. It is the human nature (or Murphy ’s law).

• CREATE A CUSTOM PAGE AND USE UNBOUNDED VARIABLES

The fields included in the search are the machine gun bullets. Reducing the number of the bullets means discharging the machine gun.

To improve the search performance, it is necessary to reduce the number of the column where Search feature is operational (in other words, reduce the number of OR – COLLATE – LIKE). The easiest way to do so is to replace the (bounded) fields in the list with (unbounded) global variables.

A practical and simple example:

• Clone Item List page and change name and Id.
• Declare the same unbounded variables.

• Assign the fields in a method that runs in OnAfterGetRecord

• Substitute field references with variables. Below an example:

In this way, the same search sentence falls back down to 2 seconds (from the initial 5).

This solution has disadvantages (trade-off): variables do not admit sorting nor filtering. But at least it guarantee the needed decent performances.

• CHANGE VISIBILITY FOR THE COLUMNS

Compared with the above, it is more an extreme measure since it will keep columns not visible. Moreover, users could make them visible through personalization. But it could also be another solution too.

In my letter to Santa (IDEAS), I would like to have a quick configuration pane to toggle on / off in which field the search feature should go through for a specific page. Default would be good as it is now, but could be changed at will, like in such cases, without the need of any AL development.