Microsoft has just released Update Rollup 10 for Microsoft Dynamics CRM 2011. One of the main areas we have been testing with is within quick finds (searching) using the Outlook Client. To further explain, please understand that the Microsoft Dynamics CRM 2011 Outlook Client uses a completely different SQL query than its Web client counterpart prior to Update Rollup 7. This is a reason to immediately move to at least Update Rollup 7. Please note that when downloading CRM 2011 Server, Update Rollup 6 is now built-in to base code revision.

In our test Scenario below, just updating from Update Rollup 5 to Update Rollup 8 provided immediate performance improvements right off the bat, and the query executing provided similar performance results in either the Outlook Client or the Web Client

Example 1 Results:
1.5 Million Rows, Quick Search Contact Name, Rollup 5, Outlook Client 37.6 seconds, Web Client 11.0 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 8, Outlook Client 8.6 seconds, Web Client  8.7 Sec
– This is a drastic improvement just applying the Update Rollup.
– Note: This customer also has another issue that I will address in this post, please continue on..

Unfortunately, due to the nature of how the scripts are dynamically created, there is still limited performance improvements with Update Rollup 8.  Update Rollup 10 is really where the magic takes place. Please note that we have tested ONLY an Outlook Client using Rollup 10 (this changes the query being sent by the client) and have noted significant improvements.

We do not recommend this approach for a production system as the server should ALWAYS be on a higher or equal update rollup then the client machines, and there could be harmful client issues.

Update Rollup 10 completely changes how the dynamic SQL scripts are created & executed. With the old query structure, all of the columns were executed together, hence SQL Server was unable to generate a good query execution plan because of all the join statements required.

Now, with Update Rollup 10, the dynamic SQL statements use separate SQL statements combined using SQL UNIONS. This change now allows for drastic performance improvements, taking advantage of SQL Server Native Execution Plans, giving us the ability to now further tune and build indexes for these additional unions avoiding complete SQL table scans.

Old SQL Query

where ((((“contact0”.StateCode = @StateCode0)) and ((
“contact0”.ParentCustomerIdName like @ParentCustomerIdName0 or
“contact0”.MiddleName like @MiddleName0 or
“contact0”.LastName like @LastName0 or
“contact0”.FullName like @FullName0 or
“contact0”.FirstName like @FirstName0 or
“contact0”.EMailAddress1 like @EMailAddress10 or
“contact0”.new_ContactIdentifier like @new_ContactIdentifier0 or
“contact0”.Address2_Line1 like @Address2_Line10 or
“contact0”.Address1_City like @Address1_City0

New SQL Query

where ((((“contact0”.StateCode = @StateCode0)) and ([contact0].[ContactId] in (
SELECT [ContactId] from [ContactBase] as “contact0” where
(“contact0”.ParentCustomerIdName like @ParentCustomerIdName0) OR
(“contact0”.MiddleName like @MiddleName0) OR
(“contact0”.LastName like @LastName0) OR
(“contact0”.FullName like @FullName0) OR
(“contact0”.FirstName like @FirstName0) OR
(“contact0”.EMailAddress1 like @EMailAddress10)
UNION SELECT [ContactId] from [ContactExtensionBase] as “contact0” where
(“contact0”.new_ContactIdentifier like @new_ContactIdentifier0)
UNION SELECT [ParentId] from [CustomerAddressBase] as “contact0” where
(“contact0”.Line1 like @Address2_Line10 and “contact0”.AddressNumber = 2 and “contact0”.ObjectTypeCode = 2) OR
(“contact0”.City like @Address1_City0 and “contact0”.AddressNumber = 1 and “contact0”.ObjectTypeCode = 2)))))

Example 2 Results:
1.5 Million Rows, Quick Search Contact Name, Rollup 8, Outlook Client 8.6 seconds, Web Client 8.7 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 2.6 seconds, Web Client 2.6 Sec
– Again another Drastic search improvement across the board.

Finally, still believing we can get even more performance out of the system, we researched more into the specific fields in the customers quick find view. The quick find view was using a search (find) field that was in the Contact Extension Base. Upon removing this field from the quick find view, the results where even more impressive:

Example 3
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 2.6 seconds, Web Client 2.6 Sec
1.5 Million Rows, Quick Search Contact Name, Rollup 10, Outlook Client 1.6 secs, Web Client 1.7 Secs The response is so fast I can barely hit my stop watch and enter key at the same time!

In closing, further tuning of this field, now that we can identify the union, should allow us to re-add the field and continue SQL performance tuning using the Database Tuning Advisor (future article on this). I would recommending reviewing any fields in the extension base and consider their value before adding them to the quick find, and at least trying to performance tune those results prior to release.

This is quick way to lose system performance by a user adding fields to the quick find (search) that are outside the main entity.

I’d like to thank Special thanks to Eric Hagen from Microsoft support, and my Tribridge team mate Mike Hauck & Wife for working many late evenings with me, researching and testing while putting his new born daughter to sleep on conference calls! Dedication 🙂