Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
In all supported versions of Business Central and in 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.
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 Stefano Demilliano already wrote in his 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.
Business Applications communities