Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
A frequently asked feature for NAV is some kind of advanced search feature, which allows to search multiple fields of the same record at once. For example, to search for "Contacts" which have the expression "The" either in the "Name" or "Name 2" or "Address" or "Address 2" or "Search Name" or … and so on.To accomplish this in NAV this might require a lot of programming, but the filters which have to be used are actually – from a SQL Server performance point – quite inefficient: wildcards have to be used on multiple filter for each field, looping through several result sets and so on. In most cases this provokes partial or full index scans on the SQL Server, causing lot of I/O (logical & physical), wasting CPU time – hence, performance is degraded.
SQL Server actually provides such an "advanced search feature" out of the box: the "Full Text Search". The following should briefly introduce, how this feature could be used with NAV – for more detailed information about FT please refer to the SQL Server "Books Online".
But first I would like to thank my pal Baris C. (a big HELLO to Switzerland J) for pointing me into the right direction with this matter!
Here we go …
-- Enable Database for "FullText-Indexing"
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
EXEC [Navision].[dbo].[sp_fulltext_database] @action = 'enable'
At this point it should be mentioned that most of the following could also be performed/enabled/created/etc. with SSMS wizard but I will show all using TSQL code.
Intensive use of the FT could produce a lot of data which could affect performance. To store the FT data – the "Full Text Catalog" - I create a new file-group/file in the NAV database. This allows to store the FT file on a separate disk/drive to separate the I/O from the "real" NAV stuff:
-- Create new File/Filegroup for FullTextCatalog
ALTER DATABASE [Navision] ADD FILEGROUP [FullTextCatalog]
ALTER DATABASE [Navision] ADD FILE
( NAME = N'NAV_403_FTC', FILENAME = N'D:\Databases\NAV_403_FTC.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
TO FILEGROUP [FullTextCatalog]
Then one could create the "Full Text Catalog":
-- Create new FullText-Catalog
CREATE FULLTEXT CATALOG Navison_FTC
ON FILEGROUP FullTextCatalog
IN PATH 'D:\FTC'
WITH ACCENT_SENSITIVITY = OFF
Now the database is ready to create "Full Text Indexes" on the relevant tables. In my example I'll create such an FTI on the "Contact" table. To do this, the table must have a unique "Primary Key" existing of one single field.
-- Create new FullText-Index , store FTIdx in new Filegroup
CREATE FULLTEXT INDEX ON [dbo].[CRONUS 403$Contact]
([Name], [Name 2], [Search Name], [Address], [Address 2], [City])
KEY INDEX [CRONUS 403$Contact$0]
WITH CHANGE_TRACKING AUTO
This index could contain all text fields ("varchar" with NAV/SQL) which are required for the search, I just use some of them in my example.The FT indexes might be updated periodically; this could happen FULL, UPDATE or INCREMENTAL, for example:
-- Update FullText Index
ALTER FULLTEXT INDEX ON [dbo].[CRONUS 403$Contact] START FULL POPULATION
From now on this FT index is ready and could be used with SQL!
To use the FTI in queries one need to use the CONTAINS statement (see "Books Online") in the WHERE clause; here an example using Dynamic SQL:
-- Test; using FTC via "CONTAINS" using "Dynamic SQL"
DECLARE @SearchExpression NVARCHAR(30)
SET @SearchExpression = 'The'
'SELECT [No_], [Name], [Name 2], [Search Name], [Address], [Address 2], [City]
FROM [dbo].[CRONUS 403$Contact]
WHERE CONTAINS(*, ''"' + @SearchExpression + '*"'')')
This simple query retrieves all records where the term "The" is included in [Name] OR [Name 2] OR [Search Name] OR [Address] OR [Address 2] OR [City]!To use such a query in NAV one could create a Stored Procedure:
-- Stored Procedure for use with NAV
CREATE PROCEDURE [dbo].[ssi_ft_contactsearch]
WHERE CONTAINS(*, ''"' + @SearchExpression + '*"'')') -- prefix search
(Please regard that this SP just returns the "Contact" "No." – this is to minimize the size of the result-set sent to NAV)
Now we could call this SP in NAV using MS ADO (Microsoft ActiveX Data Object)! ADO could also retrieve the result from the SP and process it. Excerpt from the C/AL code:
Statement := 'EXEC ssi_ft_contactsearch ''' + SearchExpression + '''';
(The "SearchExpression" is set by the user)
ADORecSet := ADOConnection.Execute(Statement, RecordsAffected, Options);
// Process Result-Set
ADOFields := ADORecSet.Fields();
ADOField := ADOFields.Item(0);
IF Contact.GET(FieldValue) THEN BEGIN
ContactTmp := Contact;
This means, the "Contact" "No." are retrieved from the SP using the "Full Text Search". Then these "No." are used to completely query the full "Contact" records to save them in a temporary table. This temp. table could be used to open a List-Form to display the result:
That's it! The advantages are this: less programming in NAV and higher performance for the search!
Have in mind that all this above is just an example; there might be a lot to add or change. So please feel free to improve this – I appreciate your feedback!
Please find attached the TSQL and C/AL examples - PROVIDED AS IS, USE AT OWN RISK, NO WARRANTY, NO GUARANTEE, NO SUPPORT.
Well I know this blog post is a few years old, but that's why I would like to hear if something like this "Google Search" way of searching from withing NAV was possible today. Or if anyone has created something like this already.
I'm afraid this is not possible. Maybe in future versions of NAV ...
I don't think that such an add-on may exist ...
Is there also a search tool on NAV available which gives suggestions based on the input which can be faulty like google search does : do you mean - name
Hope someone responds...
This is a great one! Looking forward to try it out!