NAV/SQL Performance - My Two Cents

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 …

Prerequisites:

  • The Full Text Service must be installed and running
  • The NAV database – in my example called "Navision" – must be enabled for FT; this is a database property one could set in SSMS or TSQL:

    -- Enable Database for "FullText-Indexing"

    USE [master]

    GO

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

    EXEC [Navision].[dbo].[sp_fulltext_database] @action = 'enable'

    GO

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

USE [master]

GO

ALTER DATABASE [Navision] ADD FILEGROUP [FullTextCatalog]

GO

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]

GO

Then one could create the "Full Text Catalog":

-- Create new FullText-Catalog

USE [Navision]

GO

CREATE FULLTEXT CATALOG Navison_FTC

ON FILEGROUP FullTextCatalog

IN PATH 'D:\FTC'

WITH ACCENT_SENSITIVITY = OFF

AS DEFAULT

GO

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]

ON Navision_FTC

WITH CHANGE_TRACKING AUTO

GO

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

USE [Navision]

GO

ALTER FULLTEXT INDEX ON [dbo].[CRONUS 403$Contact] START FULL POPULATION

GO

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"

USE [Navision]

GO

DECLARE @SearchExpression NVARCHAR(30)

SET @SearchExpression = 'The'

EXEC (

'SELECT [No_], [Name], [Name 2], [Search Name], [Address], [Address 2], [City]

FROM [dbo].[CRONUS 403$Contact]

WHERE CONTAINS(*, ''"' + @SearchExpression + '*"'')')

GO

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

USE [Navision]

GO

CREATE PROCEDURE [dbo].[ssi_ft_contactsearch]

@SearchExpression NVARCHAR(30)

AS

EXEC (

'SELECT [No_]

FROM [dbo].[CRONUS 403$Contact]

WHERE CONTAINS(*, ''"' + @SearchExpression + '*"'')') -- prefix search

GO

(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

ADORecSet.MoveFirst;

REPEAT

ADOFields := ADORecSet.Fields();

ADOField := ADOFields.Item(0);

FieldValue:= ADOField.Value;

 

IF Contact.GET(FieldValue) THEN BEGIN

ContactTmp.INIT;

ContactTmp := Contact;

ContactTmp.INSERT;

END;

 

ADORecSet.MoveNext;

UNTIL ADORecSet.EOF;

 

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:

FORM.RUNMODAL(0, ContactTmp);

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.

FT_Example_ContactSearch.zip