Full-text Indexing for CRM 2015 Quick Finds

  • Article

Microsoft Dynamics CRM 2015 Update 0.1 provides Full Text indexing for On Premise organizations.
Enhanced search experience with improved query performance can be achieved using this new feature.

Test 1:
Consider the following scenario: CRM 2015 + Update 0.1 + 200.000 contacts (created using a test application).
User is querying contact that contains david using wildcard in a Quick Find.

*david*

Results in testing environment (Full Text search disabled) 

(51 row(s) affected)
Table 'CustomerAddressBase'. Scan count 0, logical reads 165
Table 'ContactBase'. Scan count 12, logical reads 29080
SQL Server Execution Times: CPU time = 3641 ms, elapsed time = 3737 ms.

 exec sp_executesql N'WITH __QuickFind__ as (select top 10001 [ContactId] from ( 
SELECT "contact0".[ContactId] AS [ContactId] FROM [ContactBase] AS "contact0" WITH (NOLOCK) 
where ("contact0".MobilePhone like @MobilePhone0) OR ("contact0".LastName like @LastName0) OR ("contact0".FullName like @FullName0) OR ("contact0".FirstName like @FirstName0) OR ("contact0".EMailAddress1 like @EMailAddress10) OR ("contact0".Telephone1 like @Telephone10)) as [__QuickFindInternal__])select 
top 51 "contact0".FullName as "fullname", "contact0".ParentCustomerId as "parentcustomerid", "contact0".Address1_City as "address1_city" 
, "contact0".Address1_Telephone1 as "address1_telephone1", "contact0".Telephone1 as "telephone1", "contact0".EMailAddress1 as "emailaddress1" 
, "contact0".ContactId as "contactid", "contact0".ParentCustomerIdName as "parentcustomeridname", "contact0".ParentCustomerIdYomiName as "parentcustomeridyominame" 
, "contact0".ParentCustomerIdType as "parentcustomeridtype", case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__] 
from Contact as "contact0" WITH (NOLOCK)  
where [contact0].[ContactId] in (select [ContactId] from [__QuickFind__]) and (((("contact0".StateCode = @StateCode0)))) order by 
"contact0".FullName asc , "contact0".ContactId asc',N'@StateCode0 int,@MobilePhone0 nvarchar(200),@LastName0 nvarchar(200),@FullName0 nvarchar(200),@FirstName0 nvarchar(200),@EMailAddress10 nvarchar(200),@Telephone10 nvarchar(200)',@StateCode0=0,@MobilePhone0=N'%david%',@LastName0=N'%david%',@FullName0=N'%david%',@FirstName0=N'%david%',@EMailAddress10=N'%david%',@Telephone10=N'%david%' 

Results in testing environment (Full Text search enabled) 

(51 row(s) affected)
Table 'CustomerAddressBase'. Scan count 0, logical reads 165
Table 'ContactBase'. Scan count 12, logical reads 3040
Table 'fulltext_index_docidmap_693577509'. Scan count 0, logical reads 6080
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 22 ms.

 exec sp_executesql N'WITH __QuickFind__ as (select top 10001 [ContactId] from (            
SELECT "contact0".[Key] AS [ContactId] FROM ContainsTable([ContactBase], (MobilePhone, LastName, FullName, FirstName, EMailAddress1, Telephone1), @MobilePhone0) AS "contact0") as [__QuickFindInternal__])
select top 51 "contact0".FullName as "fullname", "contact0".ParentCustomerId as "parentcustomerid", "contact0".Address1_City as "address1_city"
, "contact0".Address1_Telephone1 as "address1_telephone1", "contact0".Telephone1 as "telephone1"            
, "contact0".EMailAddress1 as "emailaddress1", "contact0".ContactId as "contactid"            
, "contact0".ParentCustomerIdName as "parentcustomeridname", "contact0".ParentCustomerIdYomiName as "parentcustomeridyominame"            
, "contact0".ParentCustomerIdType as "parentcustomeridtype"            
, case when (select COUNT(*) from [__QuickFind__]) = 10001 then 1 else 0 end as [__QuickFindLimitValue__]             
from Contact as "contact0" WITH (NOLOCK)  
where [contact0].[ContactId] in (select [ContactId] from [__QuickFind__]) and (((("contact0".StateCode = @StateCode0)))) 
order by "contact0".FullName asc            
, "contact0".ContactId asc',N'@StateCode0 int,@MobilePhone0 nvarchar(200),@LastName0 nvarchar(200),@FullName0 nvarchar(200),@FirstName0 nvarchar(200),@EMailAddress10 nvarchar(200),@Telephone10 nvarchar(200)',@StateCode0=0,@MobilePhone0=N'((("david*" OR FORMSOF(FREETEXT, "david"))) OR ("david"))',@LastName0=N'((("david*" OR FORMSOF(FREETEXT, "david"))) OR ("david"))',@FullName0=N'((("david*" OR FORMSOF(FREETEXT, "david"))) OR ("david"))',@FirstName0=N'((("david*" OR FORMSOF(FREETEXT, "david"))) OR ("david"))',@EMailAddress10=N'((("david*" OR FORMSOF(FREETEXT, "david"))) OR ("david"))',@Telephone10=N'((("david*" OR FORMSOF(FREETEXT, "david"))) OR ("david"))'

Test 2:
User is querying cases that contains “worked”.
Cases that contains “working” and “work” are retrieved.

How to enable Full Text Search for Quick Finds:

  1. Settings > Administration > System Settings >General tab
  2. Select Yes of No next to Enable full-text search for Quick Find.

Important
It is recommended to review the following articles before enabling Full Text search for Quick Finds:

Configure Quick Find options for the organization
Improve the Performance of Full-Text Indexes
Youtube: Microsoft Dynamics CRM 2015 Update 0.1 Full Text Search Quick Find Performance Feature
Dynamics CRM 2015 Update 0.1 Full Text Search Quick Find Performance Feature