In this article, I would like to review some fetch XML expressions for operator LIKE and ways you can use to improve your search capabilities. I will also explain how indexes work and what are the solutions you can use when Dynamics Customer Engagement uses scan instead of seeking operation.

Generally, If you are good in SQL, I’m pretty sure that you are aware of it as expressions are the same as in SQL. Let’s review Fetch XML operators which you can use with the LIKE operator in Dynamics CE. Let’s say that we are looking for contacts using the following fetch XML.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="contact">
        <attribute name="firstname" />
        <attribute name="lastname" />
        <order attribute="fullname" descending="false" />
        <filter type="and">
            <condition attribute="firstname" operator="like" value="T%" />
        </filter>
    </entity>
</fetch>

I will replace the value of the operator “like” with the examples below.

Symbol	Description	Example
%	The percent sign represents zero, one, or multiple characters	T% The following example finds the customers whose first name starts with the letter T.
_ (the underscore)	Any single character	__W% The underscore represents a single character. For example, the following statement returns the first name, where the third character is the letter W.
[list of characters]	Any single character within the specified set	[PA]% For example, the above query returns the customers where the first character in the first name is P or A
[character-character]	Any single character within the specified range	[A-D]% For example, the above query finds the customers where the first character in the first name is the letter in the range A through D:
[^Character List or Range]	Any single character not within a list or a range	[^A-X]% For example, the above query returns the customers where the first character in the last name is not the letter in the range A through X.

Every time you search through Dynamics CE it tries to use SQL indexes. Indexes are used to find rows with specific column values quickly. Without an index, SQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, SQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially. By simply trying to use different operators, you can find that some queries are faster than another. Ex. test% will be executed faster than %test% or %test. This is because the first request is Sargable comparing to others.

Now let’s talk a bit more about the business case which I had. We’ve uploaded a large database of vehicles and customer had to search through this database in Dynamics CE. We identified columns which had to be used for search, and one of the columns was VIN. VIN is a unique identifier of the vehicle. VINs usually start with some predefined letters for each brand. As an example for Mercedes is WD. Vin might look like this (WDBEA30DXNB111111). As you see here, we need to use %1111 because people usually are using the last part of VIN for search. However, search with such a condition might be quite slow as a system using a scan instead of seeking. The solution which we’ve implemented was quite simple. We created a column to contain the VIN in reverse order. Then during the data migration, we had the plugin on pre-operation, which just did this reversing operation. If you are not doing any service calls then Pre-operation plugins are not decreasing the performance of the migration procedure. That’s mean that you have initial VIN and you can simply change it like you need during data migration or record creation. After data migration, we were able to utilize seek operation, and our requests were quite fast.

• fetchxml
• SDK