D365 BC F.A.Q. 3 | How to avoid search timeout? Seek and you shall find!

In all supported versions of Business Central formally known as NAV, all list pages have a Quick search control, allowing you to create a filter, that finds records containing what is typed in the search box. This is a popular feature, but it has one major problem: It performs very poorly with large tables.

How to avoid search timeout?

The problem we were facing was that we did not want to turn off this feature completely or require the developer to selectively turn off searching in some pages, as data distribution can vary a lot between installations. Hence, we decided that in Business Central Fall 18 we would add Search Timeout, limiting the maximum time such a search can consume. It almost brings the best of two worlds, as it allows us to have the search available by default in all list pages and working exactly as before 99% of the time.

As wrote in this blog, then the user will the search will be cancelled if the Search Timeout is exceeded, and the user will be presented with the notification:

In this case, the best advice to the user is to explicitly add a filter to the column, where he or she expects to find the value. This can be done from the column header or from the new Advanced Filter Pane. Another option is to pre-filter the data – e.g. to this year’s entries before applying the generic search.

You can configure the behavior in the server settings, by modifying the Search Timeout value. It is hard to give good guidance on the value to set, as it can depend on the database hardware, number of users affected, data distribution etc.

If you are on a previous version of the product (NAV 2018 and earlier), and you are having problems with users inadvertently ‘locking up’ their clients and the database by searching through tons of data, then you can instead modify the server setting UseSimplifiedFilters. This changes the behavior of the search box to only search for fields that start with the search term, and it does not force case-insensitivity. This server setting is still available in Business Central. Why we decided to put these two settings in different categories is still a mystery today.

What goes on behind the scenes (and here it gets a little technical):

The search box adds a filter clause to filter group -1 for every searchable text column displayed in the list page. Filter group -1 is special as it generates an OR statement across the column filters, whereas all other filter groups created AND statements. On top of that, the search box forces a case-insensitive search and it uses wildcard search finding data containing the search term, not just starting with the search term.

Why is this a problem:

When querying the database, the SQL statement generated will look something like:

SELECT … FROM … WHERE (“18″.”No_” COLLATE Danish_Greenlandic_100_CI_AI LIKE @0 OR “18”.”Name” COLLATE Danish_Greenlandic_100_CI_AI LIKE @1 …..

The LIKE operator (and the COLLATE operator) makes it impossible for SQL server to use any index to speed up searches and hence a full table scan is performed. This is not only a problem for the user executing the search, but also for all other users on the same database server, as the search is consuming significant database resources.

Notice that even with simplified filter it is still very likely that some of the fields searched do not have a corresponding index, leading to poor performance so for large data sets you are better off writing dedicated filters.

Re-posted from Microsoft | Dynamics 365 Business Central Blog